Designing Recoverable and Restartable SSIS Packages

Due to unexpected data, server downtime, or a myriad of other reasons, a SQL Server Integration Services (SSIS) package will inevitably fail at some point in its lifetime. Initially designing your package to handle these situations will save support time and cost. This article introduces methods intended to create a recoverable and restartable package.

SQL Server Integration Services Packages

SQL Server Integration Services, a component of SQL Server 2005 and 2008, allows developers to create packages that contain work and data flows to perform a variety of tasks, such as populating a data warehouse through an extraction, transformation, load (ETL) process and performing database maintenance on an automated schedule. Along with using connection managers to access data sources, SSIS packages are tightly tied to the metadata associated with the data sources, including the object names and data types. Designing SSIS packages can be an art, and using best practices while architecting your entire solution can save you a lot of heartache in the end.

For a majority of SSIS packages, recoverability and restartability should be the top design concern.

Common SSIS package execution problems

Even if your SSIS package’s business logic has been designed perfectly, at some point a package execution will likely fail. We can group many of the reasons for a package error into several categories.

• Category 1: External System Downtime contains failures that relate to an inability to reach an external system. This could be due to the intranet/internet not responding, a server participating in an unexpected downtime, or similar reasons.
• Category 2: Unexpected Data includes problems with the source data, which contain data type mismatches and arithmetic overflows.
• Finally, Category 3: Metadata Change issues occur because of a change to a table name, column name, or data type. Metadata Change errors happen because SSIS packages are tightly tied to the metadata of the original source.

Recoverability and restartability

Recoverability is the idea that a failure of an execution will never corrupt the state of the application. In an SSIS package, this means that if an execution fails, no data is lost or corrupted, and any effects from a failed execution do not affect a later execution of the same package or any other associated package or query. No discussion of recoverability would be complete without mentioning transactions, which are typically found in databases as an application of recoverability. Following the four principles of transactional systems, atomicity, consistency, isolation, and durability, will result in a recoverable system.

Restartability allows you to restart an application to pick up where the last execution left off. In an SSIS package, if the last execution resulted in a failure, the restart will begin at the point of failure. On the other hand, if the last execution was successful, a later execution will start at the beginning of the package. If multiple tasks must run in the event of a failure, the SSIS package can be configured to handle this as well.

When to use recoverability and restartability in an SSIS package?

For a majority of SSIS packages, recoverability and restartability should be the top design concern. Whether in the form of an ETL process or performing database maintenance, the package will be managing data or manipulating something in a database. In particular, an ETL process typically needs high reliability and cannot lose a single record. If a package fails in the middle of a data load, you must have the ability to recover your data. Once you’ve designed your package to handle recoverability, you can focus on restartability without having to worry about losing any data. In an ideal system, the SSIS package will rerun without any manual manipulation of the data and begin at the location of the last failure.

Designing recoverability and restartability in an SSIS package

Because of the importance of recoverability and restartability when running SSIS packages, we will take a look at a few methods to address this concern.

Transactions

One method to create recoverability in an SSIS package is to utilize package transactions. Transactions built into SSIS use the Microsoft Distributed Transaction Coordinator (MSDTC) and can be set to run for the entire package, a container that encloses tasks, or a task itself. Similar to database transactions, any failure within these items will undo the work already completed by the other items included in the transaction.

This method ensures that any database commands enclosed in the transaction will either all execute or none of them will complete. This is especially useful in the scenario where you cannot delete a record in a staging table until you ensure the entry is in the final table, and conversely, if the entry has made it into the final table, you want to ensure the deletion from the staging table to prevent reloading. Using a transaction can assist in an error from any of the three error categories.

Keep in mind that a transaction cannot contain any file based tasks, such as create, delete, move, as it cannot rollback those particular operations. If the rollback process should include file based operations, you will need to code the opposite operation in the event of a failure in the package.

To set up a transaction in your SSIS package that will not allow dirty reads nor allow other transactions to modify the current set of data, select the package/containers/tasks that you want included in the transaction and set the properties as shown in table 1.

Table 1: Transaction properties

Database snapshot

Another method to make a package recoverable utilizes database snapshots. SQL Server 2005 first introduced database snapshots, and other relational database management systems (RDBMS) may or may not include similar functionality. In this scenario, the package takes a database snapshot, which will store any changes to the database following the time the snapshot was taken. If the package execution fails in any way, the package restores the latest snapshot, wiping out any changes that took place during the package execution, thus returning the database to its original version.

This snapshot method wipes the slate clean in the event of a failed load. It will reset the entire database in case of a partial load due to an External System Downtime or an insert failure due to Unexpected Data. You can then fix the source data if needed and rerun the package.

This method can only be used if no other applications will be modifying the database at the same time your SSIS package runs, as it does not differentiate between changes made by your SSIS package and any other application. A slight performance overhead associated with writing to the database snapshot when the database changes affects the SSIS package as well.

To implement this method in your SSIS package, add an Execute SQL Task in the Control Flow that precedes all other tasks. This task contains a T-SQL script similar to listing 1. Encapsulate all package logic in a Sequence Container, and attach a Failure precedence constraint from the Sequence Container to a second Execute SQL Task. This task contains the T-SQL script, found in listing 2, to restore and then drop the database snapshot. Set a second precedence constraint to execute on Success and attach it from the Sequence Container to a third Execute SQL Task, which contains a T-SQL script, found in listing 3, to drop the snapshot. Find a picture of a sample package using this snapshot logic in figure 1.

CREATE DATABASE AdventureWorksSnapshot ON
  ( NAME = AdventureWorks_Data,
    FILENAME = 'C:\SSIS\AdventureWorksSnapshot.ss' )
  AS SNAPSHOT OF AdventureWorks

Listing 1: Create snapshot T-SQL statement

USE master
GO
RESTORE DATABASE AdventureWorks FROM
DATABASE_SNAPSHOT = 'AdventureWorksSnapshot'
GO
DROP DATABASE AdventureWorksSnapshot

Listing 2: Restore snapshot T-SQL statement

DROP DATABASE AdventureWorksSnapshot

Listing 3: Drop snapshot T-SQL statement

Fig. 1: Database snapshot package

Create data subsets

In this method to make an SSIS package recoverable, the SSIS package contains logic to chunk the source data into smaller pieces in order to reduce the subset that does not process successfully. Each section of data is inserted into the final destination using a bulk insert command that only commits on the insertion of all records in the subset. The entire subset fails if one record fails.

This method has the benefit of completing as much of the processing as possible without corrupting the data. The SSIS package will not load the subset of data that contains Unexpected Data to allow you to fix the data before rerunning that subset of data, but all other subsets will load.

Keep in mind a few things when working with this method. If you need an aggregate value over your subsets, this could complicate your logic. Also, this could slow down your processing by creating a semi-iterative approach rather than the normally used set-based approach of loading data.

To implement one variation of this method, create an SSIS package to filter the data by modified date. Add an Execute SQL Task to the SSIS package that executes the T-SQL found in listing 4 and stores the result set into an object variable. Add a ForEach Loop that enumerates through each of the dates, assigning the value to a variable of a string data type. Within the ForEach Loop, use a Data Flow Task that performs the insert using the previously assigned variable. Find a picture of a sample package using this data subset logic in figure 2.

SELECT DISTINCT
    CONVERT(varchar(10),ModifiedDate,101) as ModifiedDate
  FROM Sales.SalesOrderDetail
  WHERE ModifiedDate NOT IN (
    SELECT DISTINCT FullDateAlternateKey
    FROM AdventureWorksDW2008.dbo.FactInternetSales fact
    INNER JOIN AdventureWorksDW2008.dbo.DimDate dim
    ON fact.OrderDateKey=dim.DateKey)

Listing 4: Distinct dates T-SQL statement

Fig. 2: Create data subset package

Checkpoints

To make a package restartable, SSIS has a feature called checkpoints that keeps track of the tasks that have executed successfully and the state of the variables and configurable values at the time of failure. If a package execution fails, the checkpoint file created will tell the package to start at the task that failed during the previous execution.

Checkpoints allow you to automatically start the package at the point of failure. If an External System Downtime caused the failure, you can restart the package and it will not need to redo any of the work it had completed successfully, and it can pick up right at the failed task. In the same vein, if an Unexpected Data or Metadata Change problem occurred, you can fix the issue, knowing that the package will get back online and runs as soon as possible.

Keep in mind that checkpoints only work with Control Flow Tasks. Also, if you need to rerun the entire package after a failed run, you will need to delete the checkpoint file. Finally, this method doesn’t allow you to pick and choose where the package next starts up; it will always start at the task that most recently failed.

To implement checkpoints in your SSIS package, set the FailPackageOnFailure property to True on each task that you would to participate in the restartability, and set the properties on the package as shown in table 2.

Table 2: Checkpoint package properties

Process control table

Creating a process control table is another way to enable restartability in an SSIS package. This process control table will contain a list of the tasks that have run successfully. If the execution of an SSIS package fails, those tasks not yet completed will not be entered into the process control table. The next time the package run, the package will only run the remaining tasks. After a successful execution, the package deletes all tasks’ records from the process control table.

The process control table gives a higher level of control over the execution of the package. You can create an SSIS package that has parallel flows and ensure the execution of each task appropriately. Also, if you determine after the SSIS package has already run that you need to rerun a task that has already run or prevent a task from running, you can simply modify the table, and the SSIS package will pick it up during its next run. This method will handle restartability that needs to occur after a package failure due to any of the three error categories.

Using this method incurs extra development time of creating the process table, handling the conditional logic within the SSIS package, and creating a standard for task name management.  This method should only be undertaken if such fine grain control as mentioned above proves necessary.

To create the process control table, run the T-SQL script found in listing 5. In an SSIS package, add a Sequence Container that contains an Execute SQL Task. This task should contain the T-SQL statement found in listing 6 and will retrieve a single row from the process control table that tells the package which tasks to run. The single row result set should put the values into string variables representative of the columns from the query. In this example, create two variables: Task1 and Task2. Connect a precedence constraint from the Execute SQL Task to another Sequence Container that uses the expression found in listing 7. This prevents the Sequence Container from running if the task name is in the process control table. Add two Execute SQL Tasks inside the inner Sequence Container, and set up a transaction on this Sequence Container to ensure both statements run atomically. The last Execute SQL Task will insert the task name into the process control table; the T-SQL statement can be found in listing 8.  Follow the same procedure of adding the inner Sequence Container and two Execute SQL Tasks to create a second task grouping. Finally, add a success precedence constraint from the outer Sequence Container to an Execute SQL Task. Use the T-SQL statement in Listing 9 to delete all tasks from the process control table once all execution finished. See the final package in Figure 3, which shows the layout and design of all tasks.

CREATE TABLE [dbo].[ProcessControl](
   [Package]  [varchar](50) NOT NULL
  ,[Task]     [varchar](50) NOT NULL)

Listing 5: Create process control table T-SQL statement

SELECT
   ISNULL(SUM(CASE WHEN Task = 'Task1'
     THEN 1 ELSE 0 END),0) AS Task1
  ,ISNULL(SUM(CASE WHEN Task = 'Task2'
     THEN 1 ELSE 0 END),0) AS Task2
  FROM dbo.ProcessControl
  WHERE Package = 'SDN_ProcessControl'

Listing 6: Get task list T-SQL statement

@Task1 == 0

Listing 7: Precedence constraint expression

INSERT INTO dbo.ProcessControl
  VALUES ('SDN_ProcessControl','Task1')

Listing 8: Update process control table T-SQL statement

DELETE dbo.ProcessControl

Listing 9: Delete process control table T-SQL statement

Fig. 3: Process control package

Conclusion

This article covered a number of methods that can create a more reliable SSIS package. Each method has advantages and disadvantages, as discussed in each section. As with any application, implement the methods that make the most sense for your organization and your specific package scenario. If you can use one or a combination of these methods in your original design, you will end up with a more reliable, recoverable, and restartable SSIS package.