Advanced SSIS Interview Questions

I wrote a blog about a year ago that gave some tips on basic interview questions that you may run into when interviewing for a SSIS developer job.  Well today upon many request I’m writing an advanced version of these questions.  Sorry for the delay but I’ve been writing a book, which will be available in July, and most of my free time has been spent on that.  So let me know if this is useful and I'll try and come up with another list of questions which hopefully won’t take another year to write.  Also if you have any suggestions give me feedback on what can be added to the list. 

You’ll notice these will ask you to demonstrate or whiteboard a question because this is a popular technique in more advance job interviews.

SSIS Interview Questions

1.  Demonstrate or whiteboard how you would suggest using configuration files in packages.  Would you consider it a best practice to create a configuration file for each connection manager or one for the entire package?

2.  Demonstrate or whiteboard how checkpoints work in a package.

3.  Demonstrate or whiteboard using a loop in a package so each file in a directory with the .txt extension is loaded into a table.  Before demonstrating this tell which task/container accomplishes this and which enumerator will be used.  (Big hint on which task/container to use is that it requires and enumerator)

4.  Demonstrate or whiteboard how transactions work in a package.

5.  If you have a package that runs fine in Business Intelligence Development Studio (BIDS) but fails when running from a SQL Agent Job what would be your first guess on what the problem is?

6.  What techniques would you consider to add auditing to your packages?  Your required to log when a package fails and how many rows were extracted and loaded in your sources and destinations.

7.  What techniques would you consider to add notification to your packages?  Your required to send emails to essential staff members immediately after a package fails.

8.  Demonstrate or whiteboard techniques you would use to for CDC (Change Data Capture)?  Tell how you would write a package that loads data but first detects if the data already exists, exists but has changes, or is brand new data for a destination.

9.  Explain what breakpoints are and how you would use them.

Look for the answers to these question in an upcoming blog.