SSIS interview questions and answers




Here you will find "Mixed" level SSIS Interview questions and answers.

The best results achieve people who study and practice their skills and I would recommend to avoid simple memorization of answers.

Questions are split into 3 levels:

SSIS Questions For Freshers

Question 1: What is the difference between Control Flow and Data Flow? [Answer]

Question 2: Name all types of containers in SSIS? [Answer]

Question 3: What is Precedence Constraint and give all available options. [Answer]

Question 4: Name all types of connections you have worked with in SSIS. [Answer]

What to easily improve your SSIS skills?

SSIS Questions for Experienced

Question 5: What is the difference between the following SQL Server connections: OLEDB, ODBC and SQLClient? [Answer]

Question 6: Name all types of transformation you have worked with in SSIS. [Answer]

Question 7: What is the difference between Union and Merge? [Answer]

Question 8: What is the difference between Lookup and Merge Join? [Answer]

Question 9: What is a data flow buffer? [Answer]

Question 10: How can you ensure your package works when you move it to another server? [Answer]

Question 11: How can you deploy SSIS Package (or Project). [Answer]

What to easily improve your SSIS skills?

SSIS Scenario based Questions

Visitors have asked for scenario based real life questions and we have done just that. Visit Scenario Based SSIS Questions.

Interview Answers

Question 1: What is the difference between Control Flow and Data Flow?

Answer: Control Flow manages flow of tasks in a package whereus Data Flow most frequently moves data from Point A(s) to Point B(s) and in between often performs transformations, this can get more completed with multiple sources and destinations and more advanced transformations. 

Question 2: Name all types of containers in SSIS?

Answer: In our Containers Tutorial we discuss SequenceFor Loop and For Each Loop so there are 3 but some say 4 as Package itself is a container as well.

Question 3: What is Precedence Constraint and give all available options?

Answer: In our Precedence Constraint Tutorial we explain that this option allows us to control "the flow" of tasks. Constraint is the most common option but we also have Expressions that are frequently used with Variables and then we have combination of Constraint and/or Expressions. Final option is Multiple Constraints with Logical Or but that is not all as we can also combine it with Containers for those extra complex tasks.

Question 4: Name all types of connections you have worked with in SSIS.

Answer: OLEDB, .net SQLClient, Flat File, Excel, ODBC, XML and there are several more including 3rd party components like SalesForce.

Question 5What is the difference between the following SQL Server connections: OLEDB, ODBC and SQLClient?

Answer: Performance (you can check our Read Test and Write Test) and they have different way of implementation so generally SQLClient is the most friendly but can be slower comparing to OLEDB.

Microsoft also announced that they want to deprecate OLEDB (for SQL Server only) and will use ODBC instead which is painfully slow. I haven't tested this aspect in SSIS 2014 yet but I have a feeling they haven't changed anything.

Question 6: Name all types of transformation you have worked with in SSIS.

Answer: There are few and you can find most of them in our Transformation Tutorial. The most common are: Derived ColumnAggregateConditional SplitSortLookupMerge Join and Union All and for data warehouse project is SCD.

Question 7: What is the difference between Union All and Merge Transformation?

Answer: Union All and Merge are similar but Merge requires sorted input and accepts only 2 inputs whereus Union All can accept more than 2.

Question 8: What is the difference between Lookup and Merge Join Transformation?

Are you a paid member?

Log in or Buy Membership
Unlimited use access passes from $9
and long term discounts up to 87%





Did you find this page useful? +20  |  -0
(20 Votes)

good
your answer is easy to understand for beginners
good
knowlege with simple way
good contents
good
helpful,clear n to the point
The article was helpful.