SSIS Interview Questions

Entry Level Questions

1. What is the Control flow?
2. What is a Data flow?
3. How do you do Error handling in SSIS
4. How do you do Logging in SSIS? How do you do Custom Logging?
5. How do you deploy SSIS packages? And explain different deployment strategy?
6. How do you schedule SSIS packages to run on the fly
7. How do you run stored procedure and get data
8. Give a scenario: Want to insert a text file into database table, but during the upload want to
   change a column called as months - January, Feb, etc to a code, - 1,2,3.. .This code can be read
   from another database table called months. After the conversion of the data , upload the file. If
   there are any errors, write to error table. Then for all errors, read errors from database, create a file, and mail it to the supervisor.
9. What are variables and what is variable scope?
10. How do you debug a Packages
11. Explain Package configuration and how do you create custom configurations: XML and DB
12. How do you control flow and data flow in Transactions
13. Explain Multicast transformation –
14. Identify True or False - Using a checkpoint file in SSIS is just like issuing the CHECKPOINT
    command against the relational engine. It commits all of the data to the database.
15. How do you do set of operations in a Transaction?
16. Can you explain the Import\Export tool wizard?
17. What are the command line tools to execute SQL Server Integration Services
    packages?
18. 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?

For experienced candidate

1. True or False: SSIS has a default means to log all records updated, deleted or inserted on a per table basis.
2. What is a breakpoint in SSIS? How is it setup? How do you disable it?
3. Can you name 5 or more of the native SSIS connection managers?
4. How do you eliminate quotes from being uploaded from a flat file to SQL Server?
5. Can you name 5 or more of the main SSIS tool box widgets and their functionality?
6. Explain the pros and cons of deploying to a file system vs MSDB?
7. Explain architecture of SSIS?
8. Difference between Control Flow and Data Flow?
9. How to pass property value at Run time? How do you implement Package Configuration?
10. What are new features in SSIS 2008?
11. How would you pass a variable value to Child Package?
12. What is Execution Tree?
13. What are the points to keep in mind for performance improvement of the package?
14. How would you configure a data flow task so that it can transfer data to different table based on the
    city name in a source table column?
15. Difference between Union All and Merge Join?
16. Explain Lookup, fuzzy lookup, fuzzy grouping transformation?
17. How would you restart package from previous failure point? What are Checkpoints and how can we implement in SSIS?
18. Difference between asynchronous and synchronous transformations?
19. Asynchronous transformation have different Input and Output buffers and it is up to the component designer
    in an Async component to provide a column structure to the output buffer and hook up the data from the
    input.
20. How to achieve multiple threading in SSIS?
21. What is the default string data type of Excel source?
22. How to copy and rename a file to a dynamic location using SSIS?