SSIS has been used for a while now in the business, and many people worked with SSIS. However their level of working and experience and knowledge of SSIS is not the same. There are people who only touched this tool, as well as people who know SSIS inside-out. As a BI or ETL manager and team leader you might face a challenge finding good SSIS developers or consultants for your team, because everyone tell you that he/she is expert in SSIS! There is no wonder actually, this tool has been first released in 2005, and after 10 years many people worked with it. Even a server administrator might work with it for a simple data transfer work.

In programming language world let’s say C# or VB.NET you would also see many people worked with .NET. They would express that they know .NET, however it is almost easy for you as a team leader to find out how deep their knowledge is and does it fits your expectation or not. You can ask them about some libraries and functions as well as some new features that are available in each version of .NET to understand if their experience is up-to-date. You can ask them some questions to understand if they are familiar with the concept of programming or they know some design patterns of it, and the list goes on. At the end of the day you would be able to tell exactly where the programmer is in terms of experience, and knowledge of that product.

My purpose of writing this post is to give you some clue about how to ask questions to understand knowledge and experience of interviewee. In spite of SSIS being around for 10 years, there are still many recruiters and interviewers who don’t know how to distinguish good SSIS developer from someone who pretend it, and choosing the wrong candidate would cause a lot of issues later on for the development team. On the other hand you might be a SSIS developer and consultant looking to step up in your career and want to be a senior in your field. You need to know what are different aspects of this tool that you need to know, what is your strength and what is your weakness. Knowing these would help you to work on your weaknesses and step up in the stairway of career growth. So if you are a SSIS developer or consultant, or you want to be one this post is also for you.

Question Categories

I split questions in categories to have better understanding of what I am looking for. I used this approach many years in my interviews with other people and always hired really good people. Before start I have to mention that in this post I only cover technical aspects. A main part of each interview should be behavioral questions to understand if the person matches your team, and if he or she has the personality to work with and so on. However this post only covers technical questions; Here are categories you might be usually interested in:

1. Overall understanding of SSIS
2. Years of experience with SSIS
3. Familiarity with new version of it as well as old versions
4. Deep knowledge of transformation
5. Architectural View
6. Performance tuning knowledge

Now I tell you some example questions in each category. Please note that this should not be exact question that you ask (or might be asked). Once I put a question here then they will be lost, people memorize that and use that in interview, and after few times of using that they will be useless. You should design your interview questions yourself (or plan for study for interview questions yourself) to cover all categories above. There are many blog posts about interview questions and answers, and anyone can read them in an hour and go to interview a day after! The point is that you should create your own set of questions and answers, and that would be only achieved when you know SSIS well enough.

Overall understanding of SSIS

This is usually first part of the technical interview. If the person doesn’t have an overall understanding of what SSIS is then the rest won’t be applicable. You can ask basic questions such as;

• What did you used SSIS for? (Ask for some project work that SSIS has been used, and the purpose of using it)
• Can I use SSIS for this? (instead of <this> you can ask some components that usually SSIS used for, like looping through files in a folder, or uploading files into a FTP address… )
• Can I use SSIS in an environment where my source data is in SAP? (or any other data sources or destinations. with this question understanding of different types of data sources and destination connections in SSIS data flow will be checked)

Years of experience with SSIS

This part is important, because there are some tips and tricks in using this product (as well as any other products) that an experienced developer can answer. you can ask questions like these;

• What is the character limitations in variables?
• What is the step before using Merge Join transformation?
• Can I change my connections in the package dynamically without opening it in BIDS or SSDT?
• What are limitations of using Send Mail Task?

I strongly suggest you to ask for an actual development practice at this step. Many people have only read about SSIS here and there, they never worked with it, if they sit in front of SSDT or BIDS and try to build a data flow with one a bit tricky data transformation. So ask people to do it, you don’t need to ask them to implement an end-to-end ETL, it would be massive, and there is no time for it. However ask for an implementation scenario, it can be anything. Have a computer ready somewhere that you can quickly check how interviewee works on that.

Familiarity with different versions of SSIS

You might be working (or expected to be working) in an in-house development team or consultancy environment. In both cases you usually face more than one version of SSIS, and it is important that you have the skill set in the team about multiple versions of it. So ask questions such as below:

• What is Project Deployment Model and benefits of it?
  • with this question you check the experience exposure with SSIS 2012 or higher versions and new SSIS Catalog
• What DTUTIL used for?
  • with this you check exposure of SSIS versions 2008R2 or earlier versions
• What is the scripting engine difference between SSIS 2005 and 2008?
• What are Caching options with Loopkup component in SSIS 2005?
• What is new in SSIS 2016?

Deep knowledge of transformation

As SSIS developer you will be using 70-80 percent of you time building data flows and data transformations, so it is critical that you have good knowledge in this area.

• Which data flow components you SHOULD NOT be using right away?
  • you are asking about blocking transformations that needs to be considered carefully before using
• How you can do XML transformations in SSIS?
  • this is not actually a data flow question, however it is a data transformation question
• How can you execute a T-SQL script for each data row and return a result of that and use it in SSIS
• In which situations you use Expressions? What tool do you use to check expressions before executing the package?
• What is the trick to use PIVOT transformation?

There are many, many more questions that can be asked in this area. An experienced SENIOR SSIS developer should be able to answer most of your questions in this area.

Architectural View

SSIS usually used with other tools and systems. As an architect you should have a high level view of how to use this with other systems.

• What is the best way of implementing SCD with SSIS?
• How SSIS works with a Master Data Services system in place?
• What operations can do SSIS when it combined with DQS?
• What are methods of implementing incremental load in SSIS and pros & cons of them?
• How you handle bad data rows in SSIS?

Again an experienced SENIOR SSIS developer or architect should be able to answer most of these questions.

Performance Tuning Knowledge

In SSIS you work with data, lots of data. It is important that you design and implement data flows and other components in an efficient way with the best performance and speed.

• What are blocking transformations?
• What is the down side of using Multi-Cast?
• Lookup or Merge Join? tell me scenarios that each one should be used
• What are performance consideration you apply on the data flow?
• What is the difference between SQL Server Destination and OLE DB Destination?
• How do you use buffer settings in data flow?
• How you implement scenarios with parallel data flow execution?

and many other questions like these in this area. A SENIOR SSIS developer should be able to answer them easily.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.