SSIS Interview Questions
A list of mostly asked SSIS Interview Questions is given below:
1) What is SSIS?
SSIS is a short form of ‘SQL Server Integration Services‘. It is one of the significant components of Microsoft SQL Server and was initially introduced with SQL Server 2005. SQL Server 2005 is said to be the next generation of SQL Server program after SQL Server 2000, and it replaced the DTS (Data Transformation Services) in SQL Server 2000.
SSIS is very beneficial for accomplishing a wide variety of data transformation, data integration, and data migration jobs. SSIS is also known as an ETL tool mainly used to handle tasks related to data extraction, data transformation, and load operations. Additionally, SSIS also enables developers to use it for tasks other than ETL, such as data profiling, file system manipulation, etc. Apart from this, we can perform powerful batch operations in SSIS with the support of programming languages like C#.net or VB.net. In general, most companies use SSIS to store and manage their data in warehouses, and further extract and load it whenever required.
2) What is meant by the SSIS package?
An SSIS package is an organized set of connections such as control events, data flow elements, parameters, event handlers, variables and configurations. We can usually assemble a package in SSIS using the design tools or using programming and codes. Typically, SSIS packages are created in Business Intelligence Development Studio (also called BIDS in short).
SSIS packages can merge data from heterogeneous data sources into the File System, SQL Server, or Package Store. These packages can also be used to clean and standardize data, populate data warehouses, and automate administrative tasks.
3) What are the main components/parts of the SSIS package?
The following are the main components/parts of the SSIS package:
- Control Flow
- Data Flow
- Package Explorer
- Event handler
4) What is defined by the term ‘Data Flow’ in the context of SSIS?
The term data flow is nothing but a process of transferring the data from the source location to a destination. In simple words, the flow of data pumping from the corresponding source to a target destination is referred to as the ‘data flow’. It is a simple mechanism in which the information is traveling from one point to another.
There are three components of data flow, such as Source, Transformation, and Destination. SSIS allows users to gain more control over the transformation of data from start to finish. It contains several in-built transformation components that help users clean and manipulate the data in the data pipeline. To implement a data flow task in a package, the user must first attach a data flow task to a package control flow. The data flow task is then executed within the SSIS package to create, order, and run the data flow. A separate instance of the data flow engine is created for every data flow task within the SSIS package.
5) What is defined by the term ‘Control Flow’ in the context of SSIS?
Control flow in SSIS is the graphical structure of elements that allows users to link and program different tasks using the logical connectors and control the elements’ flow. The use of control flow in SSIS helps decide how the tasks will execute. It is necessary to include at least one control flow element in the package. In simple terms, the workflow for the SSIS package is called control flow.
To control the order of execution or specify the condition for executing the next consecutive task or container in a package, we can include precedence constraints to join the package’s tasks and containers. The control flow connects the modular data-flows in a sequence of tasks to achieve the desired result.
In SSIS, there are mainly three following types of control flow elements:
- Tasks that provide functionalities (such as create backups, connect to FTP, execute scripts or SQL tasks, etc.) in packages.
- Containers that provide structures (such as sequential, for loop, for each loop).
- Precedence Constraints that join tasks, containers, and executable in a sequential control flow.
6) What is meant by Data Transformation in SSIS?
Data transformation is the critical SSIS process used to extract specific data from the corresponding data source. Once the extraction is completed, the process further helps to manage and transfer the extracted data to a separate file we have selected. The data transformation generally implements several rules and functionalities to the extracted data to derive the data for loading into the separate file or end file (also called end target).
7) What, according to you, are the most significant differences between SSIS and DTS?
| SSIS | DTS |
|---|---|
| SSIS stands for SQL Server Integration Services. | DTS stands for Data Transformation Services. |
| It has powerful and complex error handling support. | It has limited error handling support. |
| It contains message boxes in .NET scripts. | It contains message boxes in ActiveX scripting. |
| It consists of an interactive deployment wizard. | It does not have any deployment wizard. |
| It has a wide range for the number of transformations. | It has a limited number of transformations. |
| It is suitable for complete BI (Business Intelligence) integration. | It does not support any BI functionality. |
8) How will you differentiate SSIS from Informatica?
SSIS and Informatica both perform well for the medium SQL server environment and are secure enough to use. However, there are some differences:
| SSIS | Informatica |
|---|---|
| It is easy to use and maintain. | It is easy to use but difficult to maintain as compared to SSIS. |
| It can be easily implemented. | It is a bit difficult to implement. |
| It has no cost, meaning it is cost-free or free to use. | It is quite expensive. |
| When it comes to productivity, SSIS is considered a moderate option. | It has comparatively higher productivity while implementing larger applications. |
| SSIS is a newer approach. | Informatica is older than SSIS. |
9) What is the meant by SSIS Catalog? Can you deploy your packages in SSIS Catalog?
The SSIS Catalog is generally defined as the database that is used to store all the deployed packages. This is mainly used to increase the security of the stored packages. Besides, it is also helpful in handling and controlling the deployed packages.
In addition, the SSIS Catalog allows users to deploy their own packages. The packages are stored there as a centralized storage database.
10) What is ‘Task’ in SSIS?
A task is nothing but a method of a programming language used to carry out an individual component of work. In SSIS, a task is generally related to the database to achieve specific desired results.
In SSIS, the tasks are mainly divided into the following two categories:
- Database Maintenance Tasks
- Control Flow Tasks
11) Why do we use the Bulk Insert Task in SSIS?
The ‘bulk insert task’ in SSIS is mainly used to upload vast amounts of data from flat files to SQL Server. In this process, only OLE DB connections to the destination database are supported.
12) What is defined by the term ‘Container’ in SSIS?
A container is defined as the set of tasks linked logically. In SSIS, the container’s use is essential as it allows us to manage the scope of the task together.
There are mainly four types of containers used in SSIS:
- Task Host Container
- Sequence Container
- Foreach Loop Container
- For Loop Container
13) What are the different types of containers used in SSIS?
There are mainly three different types of containers used in SSIS, such as:
Sequence Container: This type of container is used to put all similar tasks in the same group. The sequence container is considered an organization container primarily used for those packages, which are more complex.
For Loop Container: This type of container is mainly used to execute any particular task to a specific number of times. The For Loop Container helps execute the same tasks several times instead of creating multiple packages or executing the entire package multiple times. For example- Assume, we want to update records for any task 10 times. We can put the task inside the ‘for loop container’ and assign a value 10 as the loop’s end value. Doing so will execute the same task 10 times within the same package.
For Each Loop Container: This type of container is used in the scenario where we want to execute the task multiple times, but we are unsure how many times a task should perform. Using ‘for each loop container’, the task is executed any number of times until it satisfies the given condition. For example, suppose we want to delete all the files inside a folder, and we are not sure about the number of files inside the folder. Therefore, we can apply ‘for each loop container’ that will go through the collection of files one by one and delete them until the collection is empty.
14) Define Precedence Constraint in the context of SSIS?
Precedence constraints are defined as commands that instruct the system to follow the specific order to execute tasks. We can define the logical sequence of all the tasks using precedence constraints. In this case, the tasks will only be executed if they satisfy the condition or order specified in precedence constraint. These constraints instruct tasks to follow specific execution routes based on the success or failure of other existing tasks.
There are three main types of indication methods:
Success: This is indicated by a green line in a control flow. The workflow will proceed in case the preceding container executes successfully.
Failure: This is indicated by a red line in a control flow. The workflow will proceed in case the preceding container’s execution fails.
Completion: This is indicated by a blue line in a control flow. The workflow will precede in case the preceding container’s execution completes. This method is independent of success or failure status.
Apart from this, when constraints are used with logical operators, the workflow will only proceed if the constraints and expression evaluate to ‘true’.
15) What do you understand by the SSIS breakpoint?
A breakpoint is an SSIS property that allows developers to pause the package’s execution in the business intelligence system. It is generally referred to as the stopping point at any specific state within the code. The use of breakpoint is widespread as it helps to suddenly stop the execution at any desired point and enables the developers to re-review the status of variables, data or the entire package.
SSIS breakpoints are generally configured in BIDS (Business Intelligence Development Studio). To apply or remove breakpoints, we are required to follow the below steps:
- First, navigate to the control flow interface within BIDS.
- Right-click on any particular object where we want to apply/remove the breakpoint.
- Click on the ‘edit breakpoint’ option.
- Set the breakpoint or disable it accordingly.
16) What do you understand by a checkpoint in SSIS?
In SSIS, a checkpoint is nothing but a saving point that helps developers to restart or load the project from any specific point. This concept is mainly used to restart the project from the point of failure instead of loading the entire project again. Suppose we are working on any file, and it suddenly crashes due to an unexpected error. If we have configured the property to ‘true’, the package will create a checkpoint.
The checkpoint generally creates a checkpoint file that contains the information about the package execution. The next time when we run the package, the package execution is restarted from the checkpoint or the point of failure. If the package executes without any error, the checkpoint and the checkpoint file are both deleted. Therefore, a checkpoint is considered as safeguards for any SSIS project, in case something goes wrong, we will have a backup.
17) For which type of container, the checkpoint data is not saved?
The checkpoint data is not saved for Loop containers, such as ‘For Loop’ and ‘For Each Loop’.
18) What type of variables can be created in SSIS?
SSIS allows us to create two types of variables, such as global variables and task-specific variables. Global variables are the one which remains available for all tasks within the complete process. Besides, variables created in tasks are known as task-specific variables and remain available only within the task.
Both of these variables are almost similar to function level variables in programming.
19) What is the role of connection managers in SSIS?
The connection managers are mainly helpful when we want to collect data from different sources and further write it to any particular destination. The connection manager helps communicate with the system and give the desired information to the system like database name, server name, data provider information, authentication mechanism, etc.
20) What are the types of Lookup Cache Modes present in SSIS?
There are mainly three different types of Lookup Cache Modes present in SSIS Lookup Transformation:
Full Cache Mode: This type of cache mode helps SSIS query the database before the beginning of the data-flow task execution. This mode is a critical part of the pre-execute phase. Besides, SSIS copies all the data from the reference table (or lookup table) into the SSIS lookup cache during full cache mode.
Partial Cache Mode: This cache mode helps SSIS to query the database against new rows from different sources. In this mode, the row is cached into the SSIS lookup cache only in the case when there is a subsequent match. Once the cache gets full, SSIS automatically starts removing existing rows based on the match and usage stats. After that, new matching rows are loaded into the lookup cache.
No Cache Mode: As the name suggests, SSIS doesn’t cache any rows in this cache mode unless there are two subsequent sources with the same lookup values. In ‘No Cache Mode’, the database is queried to get the match data/value from the reference table for each row coming through the source.
21) How can you deploy SSIS packages?
To deploy SSIS packages, we must first execute the Deployment Manifest File given by SSIS Project BUILD. Next, we are required to either deploy the entire package onto the SQL Server or File System. It is good to deploy packages onto SQL Server because it is much faster than deploying onto the File System.
Another method of deploying packages is to directly import packages from SSMS to File System or SQL Server.
22) Can you log SSIS Executions?
SSIS has several features, and one of them is logging. The logging allows the system to analyze and write log entries during the run-time events. Additionally, users can also add custom messages. However, this option is not enabled by default. Due to integration services, there is support for a wide variety of different sets, including various log options. The Integration Services help write log entries to different formats, such as text files, XML files, SQL Server, SQL Server Profiler, Windows Event Log, etc.
This means the users can log SSIS Executions. In SSIS, Logs are associated with the packages and maintained at the package level. Every task or container in a package is allowed to log information to another package log. Apart from this, we can manually enable tasks and containers in any package for logging even though the package itself is not allowed or enabled to log.
23) How does an error occur in SSIS, and what are the most critical errors in SSIS?
In most cases, the error occurs during transformation due to unexpected input of data values. There can be several different scenarios when an error may occur. For example- while applying a transformation to column data, loading data into destinations, extracting data from sources, etc.
The most critical errors commonly found in SSIS are:
- Data Connection Errors: This type of error is commonly seen when the connection manager cannot be initialized with a connection string. This can be seen in both the data-source and the data-destination, along with the control flow that uses the connection strings.
- Data Transformation Errors: This type of error is observed while converting the data from the data source to a destination (in the data pipeline).
- Expression Evaluation Errors: This type of error can usually be seen in a scenario where values evaluated at a run-time exhibit invalid performance.
24) Define a term Workflow in the context of SSIS.
In SSIS, a workflow is a sequential set of instructions or commands that instruct the program executor to follow specific flow when executing tasks and containers within an SSIS package.
25) Define Data profiling task in SSIS.
Data profiling is the process in which analysis is done to better understand the source data’s state in terms of data cleaning, data pattern identification, numbers or nulls in data, etc. The data profiling task is generally used in the starting stages of the project development cycle to ensure that it supports destination design schema. Additionally, the process also helps figure whether the data is ready to be used and where it should be used within the current development cycle. The data profiling task doesn’t help while developing standard recurring ETL packages.
26) Why do we use the Ignore Failure option in SSIS?
The ignore failure option in SSIS is mainly used to ignore the errors during the transformation process. Using this, an error is ignored, and the data row is iterated to move on to the next transformation.
This option helps in a scenario where we have incorrect data (JUNK data) coming from the source. We can use the Ignore failure option to redirect such data to another transformation instead of failing the entire package. The option simply allows us to move only valid data to the destination. Additionally, it helps to move incorrect data into a separate file.
27) Define event logging mode property in SSIS.
In SSIS, all the tasks and packages contain a property referred to as Logging Mode. The event logging mode properly allows or accept the following three values:
Enabled: This option helps us to log the components.
Disabled: This option allows us to disable the components.
UserParentSetting: This option allows us to modify or optimize the parent’s settings.
28) How can we stop any package that is running in SSIS forever?
It depends on the condition where the package is running. In case the package is running inside the SQL Agent, we can explicitly kill the entire process by implementing T-SQL. Besides, if the package is running inside the SSIS catalog, we must use the stop-operation stored method. We can either use Active Operations windows to stop the process running in the SSIS catalog.
29) What are the main steps to be following for creating deployment utility in SSIS?
Deployment is the process of changing a package state from development-mode to the executable-mode. When it comes to deploying the SSIS package, one can simply deploy it by using right-click on the Integration Services project and then clicking on ‘build’. Doing this will create a file named ‘package.dtsx’ inside the folder named ‘projectbin’.
Just similar to this, one can also create the deployment utility. The deployment utility usually helps deploy the package at either an SQL server or as a file on any other location. To create the deployment utility, one needs to follow the steps given below:
- Navigate to the project and right-click on it.
- Double-click on the ‘properties’ option.
- Select the box next to ‘True’ option under createDeploymentUitlity. Here, one can also choose the location of the path for the deployment.
- Save the changes and close the window. Next, right-click on the project again and click on the build option.
- It will create a deployment folder in the BIN folder of the main project location.
- A .manifest file will be created in the deployment folder. Next, double-click on the .manifest file and select the option to deploy the package on the SQL server.
30) Enlist different types of supported file formats and connections in SSIS.
The following are the different types of supported file formats and connections in SSIS:
- XML
- Excel
- ODBC
- OLEDB
- Flat File
- .net SQLClient