SSIS Interview Questions

SQL Server Integration Services Interview Questions

What is SQL Server Integration Services (SSIS)?

Allows you to develop data integration and workflow solutions. Apart from data integration, SSIS can be used to define workflows to automate updating multi-dimensional cubes and automating maintenance tasks for SQL Server databases.

How does SSIS differ from DTS?

SSIS is an upgraded version of DTS (Data Transformation Services) and has been completely re-written from scratch to overcome the limitations of DTS.

What is the Control Flow?

When you start working with SSIS, you first create a package which is nothing but a collection of tasks or package components. The control flow allows you to order the workflow, so you can ensure tasks/components get executed in the appropriate order.

What is the Data Flow Engine?

The Data Flow Engine, also called the SSIS pipeline engine, is responsible for managing the flow of data from the source to the destination and performing transformations (lookups, data cleansing etc.). 

Difference between control flow and data flow

Control flow deals with orderly processing of individual, isolated tasks, these tasks are linked through precedence constraints in random order. Also the output for task is either, Success, Failure, or Completion. A subsequent task does not initiate unless its predecessor has completed.

Data flow, on the other hand, streams the data in pipeline manner from its source to a destination and modifying it in between by applying transformations.

What is a Transformation?

A transformation simply means bringing in the data in a desired format.

What is a Task?

A task is very much like a method, Control Flow tasks and Database Maintenance tasks. All Control Flow tasks are operational in nature except Data Flow tasks.

What is a Precedence Constraint and what types of Precedence Constraint are there?

SSIS allows you to place as many as tasks you want to be placed in control flow. You can connect all these tasks using connectors called Precedence Constraints. Precedence Constraints allow you to define the logical sequence of tasks in the order they should be executed:

• Success (next task will be executed only when the last task completed successfully) or 
• Failure (next task will be executed only when the last task failed) or 
• Complete (next task will be executed no matter the last task was completed or failed).

What is a container and how many types of containers are there?

A container is a logical grouping of tasks which allows you to manage the scope of the tasks together.
These are the types of containers in SSIS:

• Sequence Container - Used for grouping logically related tasks together 
• For Loop Container - Used when you want to have repeating flow in package 
• For Each Loop Container - Used for enumerating each object in a collection; for example a record set or a list of files. 

What are variables and what is variable scope?

A variable is used to store values. There are basically two types of variables, System Variable (like ErrorCode, ErrorDescription, PackageName etc) whose values you can use but cannot change and User Variable which you create, assign values and read as needed. A variable can hold a value of the data type you have chosen when you defined the variable. 

Difference and similarity between merge and merge join transformation

Merge works as a Union All
Merge Join works as a JOIN (Left, Right or Full)

Explain why variables called the most powerful component of SSIS.

Variable allows us to dynamically control the package at runtime. 

What is conditional split?

As the name suggest, this transformation splits the data based on condition and route them to different path. The logic for this transformation is based on CASE statement. EG: GENDER == 'F'

Why is the need for data conversion transformations?

This transformation converts the datatype of input columns to different datatype and then route the data to output columns.

This transformation can be used to:
1. Change the datatype
2. If datatype is string then for setting the column length
3. If datatype is numeric then for setting decimal precision.

This data conversion transformation is very useful where you want to merge the data from different source into one. This transformation can remove the abnormality of the data.

• Questions pulled from : http://www.careerride.com/ssis-interview-questions.aspx