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:
- For Freshers (Beginner Level),
- For Experienced (Intermediate Level - 2 to 3 years of experience) and
- Scenario based questions
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]
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]
SSIS Scenario based Questions
Visitors have asked for scenario based real life questions and we have done just that. Visit Scenario Based SSIS Questions.
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 Sequence, For 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 5: What 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 Column, Aggregate, Conditional Split, Sort, Lookup, Merge 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?
Lookup and Merge Join again is similar but Merge Join requires sorted Input so difference in performance is main difference but holistic approach needs to be taken to decide which one is a better choice.
Update: (based on @Ko_Ver twit Reply) - I've forgoten to include that Merge Join is a join type transformation so not just Inner join but outer joins are possible + as Koen pointed out Lookup will take only first "row/value" it finds so it is not the same as Inner Join for one-to-many relationships.
Question 9: What is a data flow buffer?
Answer: SSIS operates using buffers which is kind of an in-memory virtual table to hold data. We are able to set max rows/size but this gets more interesting with blocking transformations.
Question 10: How can you ensure your package works when you move it to another server?
Answer: In SSIS 2005 till 2008 R2 (also exists in 2012) we can use Package Configuration which is commonly used with SQL Server table (or XML) and environment variable. To be honest Package Configuration was a pain in the next and I much prefer SSIS 2012 Project Deployment Model with Parameters and SSIS Catalog. For more info visit our SSIS 2012 Configuration Tutorial.
Question 11: How can you deploy SSIS Package (or Project).
Answer: In previous version of SSIS it would done either manually (or from BIDS using BIDS Helper) or using scripts, again I much prefer SSIS 2012 with Deployment Wizard although you have to deploy entire project with ALL packages so an extra step to control which packages changed is recommended.
Hope that helps