SSIS – Part 1
SSIS Interview Questions and Answers for Experienced and Freshers
Here we are publishing series of posts on SSIS Interview questions with answers for experienced and freshers . Below is the series 1.
Q. Define SSIS?
Ans:
SQL Server Integration Services — commonly known as SSIS is the new platform that was introduced in SQL Server 2005, for data transformation and data integration solutions. This replaced the DTS in SQL Server 2000.
Q. Name a few SSIS components?
Ans:
- Integration Services Projects
- Integration Services Packages
- Control Flow Elements
- Data Flow Elements
- Integration Services Connections
- Integration Services Variables
- Integration Services Event Handlers
- Integration Services Log Providers
Q. What is a project and Package in SSIS?
Ans:
Project is a container for developing packages. Package is nothing but an object. It implements the functionality of ETL — Extract, Transform and Load — data.
Q. What are the 4 elements (tabs) that you see on a default package designer in BIDS?
Ans:
Control Flow, Data Flow, event Handler and package explorer. (Parameters – 2012 Data Tools)
Q. What is a Control flow and Data Flow elements in SSIS?
Ans:
Control Flow:
Control flow element is one that performs any function or provides structure or control the flow of the elements. There must be at least one control flow element in the SSIS package. In SSIS a workflow is called a control-flow. A control-flow links together our modular data-flows as a series of operations in order to achieve a desired result.
A control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow
Data Flow:
All ETL tasks related to data are done by data flow elements. It is not necessary to have a data flow element in the SSIS package. A data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. Before you can add a data flow to a package, the package control flow must include a Data Flow task. The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package.
Q. What are the 3 different types of control flow elements in SSIS?
Ans:
- Structures provided by Containers
- Functionality provided by Tasks
- Precedence constraints that connect the executables, containers, and tasks into an ordered control flow.
Q. What are the 3 data flow components in SSIS?
Ans:
- Source
- Transformation
- Destination
Q. What are connections and connection managers in SSIS?
Ans:
Connection as its name suggests is a component to connect to any source or destination from SSIS — like a sql server or flat file or lot of other options that SSIS provides. Connection manager is a logical representation of a connection.
Q. What is the use of Check Points in SSIS?
Ans:
SSIS provides a Checkpoint capability which allows a package to restart at the point of failure.
Q. What are the command line tools to execute SQL Server Integration Services packages?
Ans:
DTSEXECUI – When this command line tool is run a user interface is loaded in order to configure each of the applicable parameters to execute an SSIS package.
DTEXEC – This is a pure command line tool where all of the needed switches must be passed into the command for successful execution of the SSIS package.
Q. Can you explain the SQL Server Integration Services functionality in Management Studio?
Ans:
You have the ability to do the following:
- Login to the SQL Server Integration Services instance
- View the SSIS log
- View the packages that are currently running on that instance
- Browse the packages stored in MSDB or the file system
- Import or export packages
- Delete packages
- Run packages
Q. Can you name some of the core SSIS components in the Business Intelligence Development Studio you work with on a regular basis when building an SSIS package?
Ans:
- Connection Managers
- Control Flow
- Data Flow
- Event Handlers
- Variables window
- Toolbox window
- Output window
- Logging
- Package Configurations
Q. Name Transformations available in SSIS?
Ans:
DATACONVERSION: Converts columns data types from one to another type. It stands for Explicit Column Conversion.
DATAMININGQUERY: Used to perform data mining query against analysis services and manage Predictions Graphs and Controls.
DERIVEDCOLUMN: Create a new (computed) column from given expressions.
EXPORTCOLUMN: Used to export a Image specific column from the database to a flat file.
FUZZYGROUPING: Used for data cleansing by finding rows that are likely duplicates.
FUZZYLOOKUP: Used for Pattern Matching and Ranking based on fuzzy logic.
AGGREGATE: It applies aggregate functions to Record Sets to produce new output records from aggregated values.
AUDIT: Adds Package and Task level Metadata: such as Machine Name, Execution Instance, Package Name, Package ID, etc..
CHARACTERMAP: Performs SQL Server column level string operations such as changing data from lower case to upper case.
MULTICAST: Sends a copy of supplied Data Source onto multiple Destinations.
CONDITIONALSPLIT: Separates available input into separate output pipelines based on Boolean Expressions configured for each output.
COPYCOLUMN: Add a copy of column to the output we can later transform the copy keeping the original for auditing.
IMPORTCOLUMN: Reads image specific column from database onto a flat file.
LOOKUP: Performs the lookup (searching) of a given reference object set to a data source. It is used for exact matches only.
MERGE: Merges two sorted data sets into a single data set into a single data flow.
MERGEJOIN: Merges two data sets into a single dataset using a join junction.
ROWCOUNT: Stores the resulting row count from the data flow / transformation into a variable.
ROWSAMPLING: Captures sample data by using a row count of the total rows in dataflow specified by rows or percentage.
UNIONALL: Merge multiple data sets into a single dataset.
PIVOT: Used for Normalization of data sources to reduce anomalies by converting rows into columns
UNPIVOT: Used for de-normalizing the data structure by converts columns into rows in case of building Data Warehouses.
References:
For more MSBI stuff please have a look at below references:
http://blogs.msdn.com/b/business-intelligence
https://sreenivasmsbi.wordpress.com
http://www.msbiguide.com
http://msbiravindranathreddy.blogspot.in
http://sqlschool.com/MSBI-Interview-Questions.html
https://www.katieandemil.com
http://www.venkateswarlu.co.in
http://www.sqlserverquest.com
http://www.msbiguide.com
HI,
Mr.Udayarumilli,Thanks for the KT.Please continue this..
Venky, Thanks Much…….
Happy Reading
http://udayarumilli.com/
Excellent Blog, I have read all your blogs. Thanks for sharing important information. Such a nice post
German Classes in Chennai
German Language Classes in Chennai
Big Data Training in Chennai
Hadoop Training in Chennai
Android Training in Chennai
Selenium Training in Chennai
Digital Marketing Training in Chennai
JAVA Training in Chennai
Big Data Training
Hi,
Thanks for uploading SSIS interview Questions. I Learn many topics from this website.
Thanks Narendra.
Happy Reading
http://udayarumilli.com/
Amazing stuff !! Really appreciate your efforts ..
Thanks Indu. Thanks for visiting our blog.
Happy Reading
http://udayarumilli.com/
Hi Indu
i am looking for help with interview, would you be able to help
present iam working on 3d modelling but i want to put the effort on msbi
can u plz tell me the life of msbi
it is having future or not
Dear Kalpana, thanks for visiting our site. MSBI can be learnt from training but remember that you need to know the way MSBI handled in enterprize environment. 3 things that plays vital role in understanding enterprize architecture. 1. Design framework 2. Optimized solution for a given problem. 3. Deployment Framework. All the best for your career turn. Thanks udayarumilli
hi kalpana, i am looking for help with interview, would you be able to help me with it
can u plz tell me best site for msbi
Do you have installation video guide for MSBI?
Thanks Raj for reaching us.
We do not have any specific videos for MSBI installation. You can get the detailed explanation from Youtube tech channels.
Happy Reading
The Team SQL
http://udayarumilli.com/
Hai Rajtreo,
Visit that link https://www.youtube.com/watch?v=wHR-Q-sxVPk
Thanks Aamala for for sharing useful info.
Happy Reading
The Team SQL
udayarumilli.com
hello aamala
i am looking for interview help, would you be interested in helping
Thanks for giving big information in msbi
Thanks allot. Good job
Thanks much Lokesh…
Happy Reading
The Team SQL
http://udayarumilli.com/
nice post thank you…
Dear Sir,
I am very much passionate about SQl and MSBI and as when I get the chance for learn something new then I go through your blog and learned lots of concept MSBI concern.
But I want to design a Data warehouse that’s a OLAP system from OLTP, getting confusion that how to start designing the DW system.Please guide me….or I need a DW project that why I can learn.
Your response will be highly appreciable for me…
Thanks & Regards
Prabhakar Pandey
Email- prabhakarpandey.100@gmail.com
Hi Prabhakar, Sorry for the delayed response and thank you so much for following us. I understand you are in learning curve and you are trying to design a data warehouse from a relational database. There are lot of articles on internet can help you in designing a new data warehouse. My suggestion is to start with a simple one: Design a simple OLTP database / Use an existing OLTP Load tables with a data with the decent size Design an OLAP to hold archival data from OLTP Design an archive process from OLTP to OLAP Create a SSIS packages… Read more »
Hi….
I’m attending interviews these days, so interviewer asked to me that is
what do you develop in ssis package?
how to say answer for this question, if you know Pls guide me sir.
Awesome
Please keep writing some more based on your practical experience.
I enjoyed reading this article and useful/helpful.
Thanks for educating community.
Thanks Kris.
Sure will comeup with more articles on practical experience.
Happy Reading
The Team SQL
http://udayarumilli.com/
Hi Mithelesh, Thanks for the reach. I understand it’s not easy to handle the interview without the real experience. But the questions we have posted on our blog are the maximum list that you need to prepare for answering theatrical questions. For scenario based questions you need to prepare one or two projects, lets say If would like to go with insurance domain, everyday we have 30 to 40 data feeds on insurance member details, policies ,claims and other details. You need to prepare a list of packages to handle that data feed . Anyone can design a new ETL… Read more »
Thanks for giving big information in msbi
Thank You so much Satish.
Happy Reading
The Team SQL
http://www.udayarumilli.com
Thanks for giving big information…
Thank you so much Mythily…..
Happy Reading
The Team SQL
http://www.udayarumilli.com
Hi to all….
I’m attending interviews these days, so interviewer asked to me that is
what do you develop in ssis package?
how to say answer for this question, Pls guide me any one….
Sesank, Sorry for the delayed response. The question “What you develop in SSIS packages?” is asked to test the depth in your experience. If you are experienced then you can answer this question by explaining your BI environment. SSIS is used in both OLTP and OLAP. In OLTP it used for building ETL packages for setting data feeds between servers and for OLAP systems it’s used to build ETL packages to load DW incremental loads etc. Further more you can explain more on internal business details, example : We have insurance client and we do get Member enrollments from distributed… Read more »
Hi Udayarumilli,
Thanks for your stuff..can you please share real time interview questions and real time experiences??
Hi Pavan,
For all range of SSIS professionals 60% questions are from these concepts. Scenario based questions plays the vital role for experienced persons. We thought of sharing scenario based but it’s very time taken as we need to show screenshots for detailed understanding. We’ll surely try our best and post those questions and answers.
Happy Reading
The Team SQL
http://www.udayarumilli.com
Hi Admin,
You have an excellent collection of SSIS interview question,
Recently we have updated all the latest SSIS interview on our post, if you like our team efforts, please add our blog post to further reference which will help your loyal visitors.
Top 100 ssis interview questions
SSIS Interview Questions for freshers and experienced
Regards,
Satish
Satish,
Will add a link to the post, can you share a link for question and answers
Thanks
SQL THE ONE team
Uday Arumilli
Hi Uday,
Thanks for the response.
Please add below links
For SSIS Interview Questions and answers: – http://www.freshers360.com/ssis-interview-questions-and-answers-for-freshers-and-experienced/
For Topic wise SSIS Interview Questions: – http://www.freshers360.com/ssis-interview-questions/
Thanks & regards,
Satish
Hi Uday,
Thanks for the response.
Please add below links for reference
ssis interview questions and answers :- http://www.freshers360.com/ssis-interview-questions-and-answers-for-freshers-and-experienced/
SSIS Interview Questions :- http://www.freshers360.com/ssis-interview-questions/
Thanks & regards,
Satish
Szia,
Grazie! Grazie! Grazie! Your blog is indeed quite interesting around SSIS Interview Questions and Answers for Experienced and Freshers I agree with you on lot of points! AWS Training USA
I’m a tutor at a college in NZ trying to use AWS educate to teach my students how to set up a webserver and your admin approval system that we use to confirm access to the students is broken
Excellent tutorials – very easy to understand with all the details. I hope you will continue to provide more such tutorials.
Regards,
Radhey
Hi Uday,
Really helpful. Thanks for your time to sharing the valuable information
Srihari,
Thanks for your kind words.
Happy Reading
SQL THE ONE Team
http://www.udayarumilli.com
[…] MSBI […]
HI Uday very good information thanks for saring learning from you site and book all DBa stuff and BI STUFF.
Santosh,
Thanks for following and for the positive feedback 🙂
Happy Learning
SQL THE ONE Team
http://www.udayarumilli.com
in two interviews I faced same question which I have mentioned below.
“Suppose you are reading file from folder and load into sql server
table during this process we need to check if the file successful load
into destination then it should move success folder and if file does
not successfully load into destination then moved to Rejected folder”
And my concern is “how will we each time check that file has been
loaded successfully or not I mean how will we check records level
successful insertion”
Hi, Thanks for reaching. Coming to your question, it can be done in multiple ways. Since we need to identify the record level success / failure status we may approach: 1. In Data Flow Redirect error rows into a separate table / Row Count transformation 2. Store the row count in a variable and increment by 1 every time it captures a new error row 3. At the end of data flow for the first file then check the Error Row Count variable value 4. If value = 0 then the file successfully loaded then we move file into Success… Read more »
SSIS Quiz, http://www.databivisuals.com/p/ssis-interview-questions.html
Vmware Training in Chennai
CCNA Training in Chennai
Angularjs Training in Chennai
Google CLoud Training in Chennai
Red Hat Training in Chennai
Linux Training in Chennai
Rhce Training in Chennai
This information is impressive..I am inspired with your post writing style & how continuously you describe this topic. After reading your post,thanks for taking the
time to discuss this, I feel happy about it and I love learning more about this topic Excellent article.
Hadoop Training in Chennai
Big Data Training in Chennai
Python Training in Chennai
Python Training Centers in Chennai
Data Science Training in Chennai
Data Science Course in Chennai
Data Analytics Training in Chennai
Best AngularJS Training in Chennai
AngularJS Training in Chennai
QlikView Training in Chennai
Informatica Training in Chennai
I simply wanted to write down a quick word to say thanks to you for those wonderful tips and hints you are showing on this site.
amazon-web-services-training-in-bangalore
This was an nice and amazing and the given contents were very useful and the precision has given here is good.
I simply wanted to thank you so much again. I am not sure the things that I might have gone through without the type of hints revealed by you regarding that situation.
https://www.besanttechnologies.com/training-courses/data-warehousing-training/hadoop-training-institute-in-chennai
Hello Mate, I am shocked, shocked, that there is such article exist!! But I really think you did a great job highlighting some of the key Relentless dreamer. Fearless optimist. Getting engaged. Where do I begin? … in the entire space. I am a freelancer and I previously created an account using a gmail address and obtained my certification with this account. During the registration process of the APN program it needs a non gmail address so i used my professional address, how can i merge or link the certifications of my first account with the APN program? I started… Read more »
This was an nice and amazing and the given contents were very useful and the precision has given here is good.
Hmm, it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I as well as an aspiring blog writer, but I’m still new to the whole thing. Do you have any recommendations for newbie blog writers? I’d appreciate it.
https://www.besanttechnologies.com/training-courses/amazon-web-services-training-in-bangalore
Thank you so much for posting sis interview questions.This helps me alot…
Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here.
Thank you for this interview question ..Helps me lot
It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me…
python training in chennai
python course institute in chennai
Thank you for benefiting from time to focus on this kind of, I feel firmly about it and also really like comprehending far more with this particular subject matter. In case doable, when you get know-how, is it possible to thoughts modernizing your site together with far more details? It’s extremely useful to me.
python course in pune
python course in chennai
python Training in Bangalore
It would have been the happiest moment for you,I mean if we have been waiting for something to happen and when it happens we forgot all hardwork and wait for getting that happened.
Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal. I must say you’ve done a very good job with this.
http://www.trainingbangalore.in/amazon-web-services-training-in-bangalore.htmL>
Wonderful article, very useful and well explanation. Your post is extremely incredible. I will refer this to my candidates…
I always enjoy reading quality articles by an individual who is obviously knowledgeable on their chosen subject. Ill be watching this post with much interest. Keep up the great work, I will be back
Data Science course in Chennai
Data science course in bangalore
Data science course in pune
Data science online course
Data Science Interview questions and answers
Data Science Tutorial
Data science course in bangalore
SSIS is a topic which always calls up for many interview questions and hence every information related to SSIS operations should be always gathered and studied as much as possible.
Attend The Python training in bangalore From ExcelR. Practical Python training in bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Python training in bangalore.
python training in bangalore
Very informative and short brief on SSIS..
I feel very grateful that I read this. It is very helpful and very informative and I Python training in pune really learned a lot from it.
This is a wonderful article, Given so much info in it, These type of articles Digital marketing in pune keeps the users interest in the website, and keep on sharing more … good luck.
APTRON Gurgaon remains as best among the Data Science training Institutes in Gurgaon. To oversee walloping measure of the data, data scientists are required who are the most eager individuals. These can deal with huge volumes of data and truly play with it, to give deductions and make spot patterns upon the data. It is the without a doubt rising field in data analysis which has incredible connection with the up and coming data software that are being prepared for the spontaneous creation of data management.
For More Info:- http://aptrongurgaon.in/best-data-science-training-in-gurgaon.html
Simply, i want to say many thanks for this post. make sure, I got great experience with your blog. It so much impressed me. Always, I welcome your post.
Web Designing company in Madurai