SSIS interview questions

How add new column at run time ?

By using derived column transformation we can add new column.

What is the use of checkpoints?

Checkpoints helps us to run the package from point of failure. This is control flow level property.

Checkpoint file is an XML file that stores failure point and its package variable name.

How to use same data connection for multiple tasks?

Set retain same connection property true for connection manger.

How to remove duplicates from flat file source to destination?

 We can do this in several ways.

 Aggregate transformation.

http://munishbansal.wordpress.com/2010/04/21/how-to-remove-duplicate-records-from-flat-file-source-ssis/

Sort transformation.

Script component.

http://microsoft-ssis.blogspot.co.nz/2011/12/redirect-duplicate-rows.html

Explain  how to deploy package from command prompt?

DTUTIL  /FILE filename.dtsx /COPY SQL; filename

What is the control flow and data flow in SSIS?

 A control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow, you use precedence constraints to connect the tasks and containers in a package. A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow.

A data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. Before you can add a data flow to a package, the package control flow must include a Data Flow task. The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package.