Professional SQL Server™ 2005 Integration Services

9.1. Restarting Packages

Everyone has been there—one of your overnight Data Transformation Services (DTS) packages failed overnight and you now have to completely rerun the package. This is particularly painful if some of the processes inside the package are expensive in terms of resources or time. In DTS, the ability to restart a package from where it left off did not exist, and picking apart a package to run just those tasks that failed was tedious and error-prone. There have been a variety of exotic solutions demonstrated, such as a post-execution process that goes into the package and re-creates the package from the failed step onward. Although this worked, it required someone with a detailed knowledge of the DTS object model, which most production DBAs did not have. If your process takes data from a production SQL Server that has a very small window of ETL opportunity, you can almost guarantee that the DBA is not going to be pleased when you tell him you need to run the extract again and that it may impact his users.

For this reason, the introduction of "Package Restartability" or checkpoints in SQL Server 2005 is manna from heaven. In this chapter, you are going to learn everything you need to know to make this happen in your SSIS packages.

Checkpoints are the foundation for restarting packages in SSIS, and they work by writing state information to a file after each task completes. This file can then be used to determine which tasks have run and which failed. More detail ...