Tuesday, August 20, 2013

SSIS Interview Questions: Checkpoint

Checkpoint is the property in SSIS which enables the project to restart from the point of failure. When we set the property to true package create the checkpoint file which stores the information about package execution and use to restart package from the point of failure.
If the package runs successfully, the checkpoint file is deleted, and then re-created the next time the package is run.

You can assign value to variables and connection manager and can also change other attributes dynamically at run time using configuration file.
For creating config file you right click on package body select the configuration option and then select a file type and attribute which you want to add in that config file.
There various options provided for handling config details such as creating XML files, using SQL Server database table environment package variables.
Package configurations provide the following benefits:
Easy to move package from one environment to other.
Helpful when there are system dependent variable which are used in package
Integration Services supports several different methods of storing package configurations, such as XML files, tables in a SQL Server database, and environment and package variables.
Each configuration is a property/value pair. The XML configuration file and SQL Server configuration types can include multiple configurations.
 Configurations are included in deployment utility for installing packages.

Checkpoint data is not saved for ForLoop and ForEach Loop containers. Whenever package restarts the For Loop and ForEach Loop containers and the child containers are run again. So as conclusion for loop a container if a child container in the loop runs successfully, it is not recorded in the checkpoint file, instead it is rerun.
There is work around for this as listed on Microsoft website: put loop task inside the sequence container task.
Now one big thing which you have to remember that checkpoint applies for control flow task only it means if there is failure in data-flow task it will rerun the data-flow task again. For example there is data-flow task where out of 50000 records 20000 transferred in to destination. At this point a general error occurs and fails the data-flow task. Now what will checkpoint do will the data-flow continue for rest records or it will start from new when package restart. Answer simply entire data-flow task will restart from beginning.

No comments:

Post a Comment