Register Login

SSIS Interview Questions and Answers

Updated May 30, 2019

What is SSIS and what does it stand for?

SQL Server Integration Services is a tool inside the Microsoft SQL Server database that is used for extraction, migration, and consolidation from many databases. It then transfers the data into other data warehouses.

What are some important components of SSIS package?

The main components of SSIS are:

  • The SSIS runtime engine
  • The SSIS object model
  • The data flow components – destinations, transformation and sources.
  • The SSIS windows service

How to create an SSIS package?

The steps to create a package in SSIS are as follows:

  • It can be created through the package template in the Integration Services.
  • Clone an existing package by copying the name and GUID of the package into the new package. The new package’s name and GUID have to be changed to avoid confusion.
  • The SQL Server import and export wizard can also be used. The wizard is used to add the data, source, destination and handling the settings for making the import and export successful. The SQL Server Data Tools can be used to improve the package. The package can be saved to be used later.
  • A custom package can be used to create a new package.

What is the use of SSIS package?

The primary use of the SSIS package is the ETL operations extraction, transfer, loading of the data from one data warehouse to another. It helps in the maintenance of data and the databases and can be used for data cleansing.

How to upgrade an SSIS Package?

To upgrade the SSIS packages the SQL Server has an SSIS Package Upgrade Wizard.

The steps to upgrade the packages are given below:

  • From the SQL Server Data Tools, run the SSIS Package Upgrade Wizard.
  • Create a new Integration Services Project.
  • Right click on the SSIS Package node in the Solution Explorer and hit Upgrade All Packages. This will start the upgrade process of the packages within this node.

What is control flow in SSIS?

The control flow is basically the workflow of the different tasks that are to be executed in a specific order. It allows the execution of different tasks while managing a sequential order among them. An SSIS package can have more than one data flows and one control flow.

Control flow elements are of various types:

  • Tasks having some functionality
  • Precedence constraints connecting tasks, containers, and executables.
  • There are containers that have packages along with structures.

What is data flow in SSIS?

Data flow is the concept that deals with the flow of the data from one source to another target destination. Data flows among the items like source, transformations, and destinations.

Iterations cannot be performed with the data flow. It works in the memory and allows the SSIS to perform faster extraction, transaction and load operations. Here, the data is cleaned through an SQL after being loaded in a staging environment.

What is a checkpoint in SSIS?

A SSIS checkpoint is a restore point used in case the system fails and data has to be recovered. Here, the recently modified dirty pages and transaction logs are stored in the physical memory.

It is useful for reduction of the recovery time during the system failure. Using checkpoints the packages can be started from the point of failure. A checkpoint is created for each database.

What is lookup transformation in SSIS?

Lookup transformation in SSIS is a tool used for input data in a column to a column in another dataset. It is used to compare the data in the existing dataset with the destination dataset and filtering out if the data that matches or not. It can also be used to view the data in tables based on the information from common columns.

What is merge join in SSIS?

The merge join in SSIS is used to perform SQL joins on two databases that are sorted. Join operations like Inner Join, Full Outer Join, Left Outer Join, and Right Outer Join.

In a data warehouse, the merge transformation is very useful as data can be loaded in the dimension tables. As it will only work with sorted data, the Sort Transformation is necessary before any kind of Join operation is applied to the data.

How to zip a file using SSIS?

The free software called 7Zip can be used along with SSIS to compress files and folders. The standalone version can be used.

After that, an archive file can be created by invoking the 7Zip with the required parameters. This script task allows more functionalities for error handling and data management. Apart from this, the Execute Process Task can also be used.

What are precedence constraints in SSIS?            

The precedence constraints in SSIS are used to link the tasks. A task will execute only after the condition set by the constraint of the preceding task is satisfied. Based on the success and failure of the tasks, the execution paths can be determined.

It connects two executables namely, constrained and precedence executable. Before the constrained executable runs, the precedence executable runs. This execution determines whether the constrained executable will run or not.

How to create a temp table in SSIS?

A temporary table can be created in SSIS through the Execute SQL task. For this, the RetainSameConection has to be set to True so that the table can be created in the Control Flow task. This table can be retained in another task.

What is a multicast transformation in SSIS?     

Multicast Transformation in SSIS is used for sending data to one or more outputs. Here, no transformation or condition is applied. Using this functionality, the package can make different logical duplicates of the data, which is useful when the package applied multiple transformations on the same data.

Multicast does not support error output but has an only single input.

What are the tasks in SSIS?

An SSIS package consists of one or more tasks. A task is the unit of work done in a package control flow. These are sequentially arranged through precedence constraints in the control flow.

The different types of tasks are:

  • Data preparation tasks 
  • Workflow tasks
  • Data flow tasks
  • SQL Server tasks
  • SQL Server maintenance tasks
  • Scripting tasks
  • Analysis Server tasks and Containers

What is the engine thread property of the data flow task in SSIS?

The Engine thread property of the data flow task is used to define the number of threads the data flow engine can execute and develop in parallel. This property is applicable for both source and worker threads.

For example, if the property is set to 5, this means 5 worker and source threads can be created by the engine.

Mention various types of files that support SSIS?

SSIS supports CSV (Comma Separated Files), Excel spreadsheet files, directory files, DTSx file, Flat file, Raw file and text files.   

What are non-blocking, semi-blocking and fully-blocking components in SSIS?  

  • Non-blocking or synchronous transformations do not change the data shape and offer high performance during data transformation.
  • Semi-blocking or asynchronous components change the shape of the data as a subset of it has to be collected before transferring it to the destination.
  • Fully-blocking components are the slowest and require all the data to be fetched from the source before transferring it to the destination.

What is the difference between checkpoint and breakpoint in SSIS?

  • A checkpoint is a restore point used in case the system fails and data has to be recovered.
  • A breakpoint is used to analyze the values of variables before and after execution.