Friday, October 08, 2010

Using Checkpoints in SSIS By Brian Knight

Here is the link of today SQL Share / Jumpstart TV video. Prior to viewing this video I had no knowledge of what checkpoints are.... here are my learnings out of this video.
What are checkpoints?

Checkpoints are setup to ensure that you can run the package from the failure point. These checkpoints are particularly useful when packages take a very long time to run.

When the package fails -- the problem can be fixed and the package is restarted. When checkpoints are used when the package is restarted the package restarts from the point where the package has failed. This will save alot of time for the DBAs and Developers.

Here is the process for creating simple check points by Brian

Step 1 Configuring the packageIn the package properties pane give a name to the property -- checkpointfilename The next property to be set is the CheckPointUsage -- Choose the If Exists option.The next property to be set is the SaveCheckPoints -- set it True
Step 2 Task propertiesChoose the task in the control flow and under the Execution group of the task properties falipackageonfailure -- TrueSet this property to True for all the tasks in the package.
This is the process of creating the checkpoints in the control flow layer in SSIS packages.

No comments:

INSERT EXEC failed because the stored procedure altered the schema of the target table.

This morning I was trying to call a stored procedure and insert the results in a temp table using the following code  -- create table #...