SSIS Interview Questions

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Microsoft SQL Server database developer, architect, and author specializing in Business Intelligence, ETL, and Data Warehousing.
Published:
Updated:
Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
Intro
Recently I was asked a question from an expert that had an interview for a SQL Server Integration Services (SSIS) position, which is Microsoft's tool for Extract-Transform-Load (ETL) applications, and the resulting comments and advice became this article.  The intent here is to not demonstrate how to ace any technical interview, as I may write that in the future, but specifically to approach an SSIS interview for maximum 'day before the interview' success. 

A Reality Check
For starters, if you are asking 'Any idea how to prepare for an SSIS interview?' in general terms the day before an interview, then you're probably not qualified for the job.   SSIS has a large learning curve as it is designed for all data sources,  so candidates are not realistically going to 'cram' the day before and fool an interviewer.  If any recruiting agency is giving you this advice and a hiring manager finds out, there is a good chance that they will be cut off from sending any more candidates. 

What you can do is prepare your past experience for instant recall and to be more presentable.  

Tip #1 - Have a demo
Bring your laptop, and be prepared to demo what you've worked with.  Past SSIS work, school assignments, or even self-study is fair game.    And if you don't have access to that work, or if it is considered confidential and not appropriate for you to have a personal copy, then create a mockup from memory with sample data will be acceptable for interviewing.   Failing that, make sure any articles or blogging you've done on the internet is presentable and demo that. 

Talking about your past work is great but the employer may not believe you, or believe that you were the main developer.  Showing them your past work nails it.   Show me ... anything that will separate you from someone that can only talk. 

Tip #2 - Questions and Answers

Technical

  • Data Sources and Destinations (aka Targets) you've connected to: SQL Server, Oracle, Informix, DB2, flat files, flat files with multiple sections such as headers and footers, Excel, XML.  Generally the more sources you can point to the better you'll look. 
  • Transformations:  Data Conversions / Derived Column, Lookup, Merge, Pivot / Unpivot, Conditional Split / Multicast.  Again, the more you've done the better you'll look, as having a firm grasp of transformations will separate you from the 'I can spell SSIS, that's it' and 'I did a basic pump and dump but nothing else' crowd of junior developers.  
  • Scripting, VB.net or C#?  For what?  Useful when you need customization that cannot be done within another transformation or expression, such as loops and function calls. 
  • How do you log SSIS package success / failure / progress?  Most large companies already have a methodology for handling logging, and error handling, so they want to hear what you've done in these areas.   This can be invaluable for mission-critical processes, prod support situations, and anything covered by a Service Level Agreement (SLA) where your company has promised a certain level of performance and can be subject to fines for not meeting them. 
  • Tell me how you performed production support on SSIS package failures, or success where the data processed was not what the client expected.  
  • What third-party tools have you used in SSIS development?  Pragmatic Works Task Factory and CozyRoc are the two I've worked with, especially Task Factory, for its rich set of custom components. 
  • Have you ever had to deal with confidential information (banking, healthcare, PII) and how did you protect it?  Encryption, not storing PII data in a physical table, etc. 
  • What other ETL packages have you worked with besides SSIS?  Talend, DataStage, Informatica, scripts. 
  • Blocking Transactions:  Any transaction that forces the entire data to be pumped into the previous transaction before the blocking transaction is processed, which forces use of more active memory.  Examples are Aggregates, Sorts, and Row Sampling. 

Design Q&A
  • What is your preference, Extract-Transform-Load (ETL) or Extract-Load-Transfer (ELT)?  The difference between these two is with ETL the data transformation logic is performed in the package before the data is inserted into the database, and with ELT data is inserted as-is into various staging or temporary tables, then transformed, then inserted into the ultimate destination.  Personally I'm in the ELT camp as I like to make sure I have everything and can perform data validations and error handling afterwords, but depending on how much data you need to pump that might not be possible.
  • When would you write code in a Stored Procedure vs. complex transforms in an SSIS package?  Essentially this is testing the limits of what SSIS can do, and any T-SQL that SSIS is not suited well needs to be written in T-SQL. 

Project management
  • Have you ever had to elicit further requirements for an SSIS project, and if so how?   I have an article out there called Requirements Document Template for an ETL Project that would help with interview prep.
  • What is a Source to Target Mapping Document?  Considered by many as the bible, it is a column-by-column mapping of the the data source, any data transformations that the ETL project performs, and the resulting data destination (aka Target).
  • Have you ever had to set up QA to review your SSIS project, and if so how?  Parameters to facilitate test runs, and separate testing environment, any needed documents. 

Tip #3 - Training
  • Where did you learn SSIS?  Most schools do not teach SSIS, and saying you learned on your own can be suspect.  Pragmatic Works Free Training and Workshops, SQL Saturday, and any of the PASS Events such as Summit, Business Analytics ConferenceSQL Saturday, 24 Hours of PASS, or local chapter sessions.  
  • Did a previous employer teach you SSIS?  Make sure you are able to tell at least one colorful story of what their SSIS packages did, how involved you were, and what they're doing with it now. 

Tricks - Witty One-Liners You Can Use In a Jam
  • "I don't know the answer to that question, but I would find it by ___"  Hiring managers expect that a candidate will not have all the answers, especially those that work in highly customized industries, and it is good to show them how you would research a problem.  Google, MSDN, Experts Exchange, ask an architect or senior developer are generally acceptable answers. 
  • "How important is that to you?"  can demonstrate that you have the ability to flush out requirements.  Maybe they want a rock star, maybe they have existing process that they will train you to use with no opportunities to deviate from those processes, and sometimes it's hard to tell the difference, so you have to ask. 
  • "I haven't dealt with that directly, but at previous clients we ____"  If nothing else they get to benchmark their processes with that of another company. 
  • "I really wish I could have done ____."   With any project there is functionality that could have been built to improve the process, but the developer was not able to do it due to timelines or budget.  Auditing, extra reporting, extra data validations, events and messaging, etc.  Show your new employer that you're motivated to build great work by describing what extra you would have built for previous employers. 
For a more technical list of interview questions check out this MSSQLTips.com series of articles.

The end.  Thank you for reading my article, and if it helped please click the 'Good Article' button. 
6
5,159 Views
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Microsoft SQL Server database developer, architect, and author specializing in Business Intelligence, ETL, and Data Warehousing.

Comments (1)

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.

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/

Regards,
Satish

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.