These SSIS interview questions cover many of the top topics that you will be asked when applying for your next SSIS developer job. Each question has links to articles that cover the topic in depth.
Introduction
Looking for a job related to SQL Server Integration Services (SSIS)? An SSIS developer in USA has an average salary of 78,414 USD, which is a good salary compared of other related jobs according the PayScale website:
Job | Salary per year (USD) |
78,414 | |
73,285 | |
71,775 | |
69,948 | |
68,535 | |
62,819 |
The question is. How can I get a job as an SSIS developer?
In this article, we will talk about interview questions to learn SSIS for newbies and also for experienced users and have enough knowledge to pass the interview questions and get a nice job.
Certification exams for SSIS
There are official certification exams from Microsoft to be an expert in SSIS. The following certifications exams can be useful to validate your knowledge and if you do not have the knowledge, you can look for the courses, books, training material related to these exams:
- Exam 70-467 Designing Business Intelligence Solutions with Microsoft SQL Server
- Exam 70-767 Implementing a Data Warehouse using SQL
These exams not only cover SSIS, but other areas like SSAS and SSRS. The skills required to pass these exams are deep.
If you pass these exams, you can pass any interview question for sure. They are difficult exams that requires a lot of experience.
SSIS Interview questions
What is your previous experience in SSIS?
The interviewer will ask you what type of SSIS jobs did you do in your previous jobs. If you do not have any experience, you must be honest. However, try to do some labs and experiments on your own. If your company does not require to use all the SSIS components, try to learn by yourself as much as possible in your free time before the interview.
You can say, that your SSIS tasks were simple, but you have enough experience about performance, parallel tasks and scripting. Try to read as much as possible to have great skills.
What is an SSIS package?
Is the storage file with .dtsx extension that contains your control flow, data flow, connections, variables, parameters, event handlers, etc in SSIS projects.
Here you have the documentation related:
What is the control flow?
The control flow is part of the package and contains tasks with functionality (create backups, execute scripts, execute SQL tasks, connect to FTP, etc.) and containers (can be sequential, for each loop, for loops) and finally it includes constraints to join flows.
For more information about the control flow, refer to this link:
What is a data flow?
The data flow allows to export data from different sources to different destinations and transform the data if necessary. There is a Data Flow component in the control flow and when you double click the task you have new tasks to import and export data.
For more information about the data flow, refer to this link:
What is an SSIS Catalog?
The SSIS catalog is a database to store all the deployed packages. It is used for security reasons to store and handle the deployed packages.
Here you have the theory related to the SSIS Catalog:
How can you deploy a package in the SSIS Catalog?
In the SSIS catalog you can deploy your packages. The packages can be stored there as a centralized storage database.
Here you have the links about package deployment in the SSIS Catalog:
- How to execute a Deployed Package from the SSIS Catalog with various options
- Deploying Packages to SQL Server Integration Services Catalog (SSISDB)
What is the script task and what is the script component? What is the difference between the two?
The first one is used to program any task in the control flow using C# or VB. The Script Component is in the Data Flow and it is used to transform columns in source and/or destination.
I know experienced SSIS developers who never programmed a Script. I strongly recommend you to learn C# or VB to increase your capabilities in SSIS. The script task and script component will save you in many scenarios.
For more information about the script task and the script component refer to these links:
- Script Task Debugging in SQL Server Integration Services (SSIS)
- How to work with SQL random numbers in SSIS
- SSIS Script component vs derived column
When do you use the Script component and when the derived column?
In general, use the derived column if the transformation is simple and the Script component if it is complex.
For more information, refer to this link:
What are the SSIS Expressions?
If you are not familiar with SSIS Expressions, it, most likely means that you are not an expert and your SSIS experience is basic, so it is important to become very familiar with this feature. SSIS Expressions can be used to filter information, to work with parameters, concatenate information, create conditions. In other words, it helps to work with dynamic connections, conditions and work with loops.
For more information about SSIS Expressions, refer to these links:
How would you stop a package that is running forever?
It depends. If you are running the package in the SQL Agent, you can kill the process using T-SQL. Alternatively, if the package is running in the SSIS catalog it can be stopped using Active Operations window or the stop operation stored procedure.
For more detailed information, refer to our article related:
How can you retrieve data of packages stored in the MSDB Database?
The packages can be stored, back up in the system database MSDB. You have system tables to get data from them. For example, the dbo.sysssispackages can give you the list of SSIS packages stored in the database. For more detailed information about the structure, msdb tables, please refer to our article related:
How would you monitor performance in SSIS?
Microsoft includes performance counters to monitor Applications. SSIS is not the exception and it includes performance counter to measure the packages executed, Blob read/write, buffer information, rows read/write and more.
For more information about this topic, refer to our link:
Note: For a SSIS performance monitoring tool, check out Foglight
Do you have experience connecting Azure to SSIS?
Microsoft offers a free Azure Feature Pack to connect to Azure. Also, other companies offer plugins for Azure to connect.
The Azure Feature Pack includes task and connectors to connect to the Azure Blob Storage, Azure Data Lake, HDInsight.
If you are not familiar with Azure, we recommend to read these articles related:
Are you familiar with 3rd party SSIS plugins?
In many cases, the default SSIS tools are enough, but in many cases, you will need to install plugins to increase to functionality to connect to Amazon, Facebook, Gmail and thousands of other sources.
There are several companies that created some extra tasks and connectors that may be useful.
I strongly recommend you to take a look to the plugins of ZappySys, CozyRoc, KingWaySoft or other companies. They offer interesting plugins to connect to REST API, XML, JSON, Amazon and thousand other sources using tasks.
Do you know how to program your own SSIS task?
If you can create your own custom classes, it means that you are a really experienced user. If you have great programming skills in C# or VB, it is possible to create your own task.
For more information about creating your own custom task, refer to this link:
Is it possible to use SSIS objects in Visual Studio and extract data without SSIS?
Yes, it is. You can have a Visual Studio project in C# like a web application a Windows desktop application, a console application and invoke SSIS objects to program tasks without SSIS projects. In other words, you could create your control flows, data flows, connections using just C# code or Visual Studio code. In most of the cases, it is not practical to do it, because it may take longer time. However, it can be necessary if the task in the SSIS project has some functionality limitation that could be covered by the code in C#.
If you already worked with the SSIS classes, it means that you are a Senior SSIS developer.
For more information about this topic, refer to these links:
Did you have performance problems in SSIS. If so, how did you solve the problem?
A typical problem with performance in SSIS is the buffer; the out of memory error. To solve performance problems, we strongly recommend you to read the article: Integration Services: Performance Tuning Techniques white paper.
Conclusions
In this article, we learned how some important SSIS interview questions that may be asked in a job interview for an SSIS position.
It is important to know that you will never know everything. Even the SSIS creators do not know everything, but it is important to learn each day more to be in the top of list to score a job in SSIS.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023