This post is part of a series on this blog that will help me, and hopefully you, pass exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012. So far, we’ve covered:
- Tables and Schemas in the data warehouse
- Dimensions and Slowly Changing Dimensions
- Fact tables and measures
- Intro to columnstore indexes
- Columnstore indexes and partitioning
- Introduction to SQL Server Data Tools (SSDT)
- Connection Managers
- Control Flow Tasks and Containers
- Data Flow Source Adapters
- Data Flow Destination Adapters
- Data Flow Transformations
- Variables and Parameters
- SSIS Expressions
Your package will fail. I’m not trying to bring you down, but this is a fact of life and you need to deal with it. Your packages need to deal with it, too.
Package Transactions
Just like you can use transactions to perform atomic units of work in SQL Server, you can use transactions inside your packages to define operations that must be completed (or rolled back) as a single unit. Transactions can be implemented at the package level, for a container, or even at the task level. To start a transaction within SSIS, you use the TransactionOption property. This property can be set to one of three values:
- Required – This setting will start a new transaction, if one doesn’t already exist. If one does exist, the component will join that transaction.
- Supported – This setting will join an existing transaction, but it won’t create a new one. This is the default setting.
- NotSupported – The component will not participate in any existing transaction.
So to start a transaction you would set this property to Required. If you’ve got a group of tasks that you want to include in a single transaction, you’ll want to create a Sequence Container and place all of those tasks inside it. Then set the TransactionOption property of the container to Required, to start the transaction. As long as you haven’t changed the TransactionOption property of the tasks to NotSupported, you’ll be good to go. One thing you should note is that, if you’re using transactions with a For Loop or Foreach Loop container, each iteration of the loop will be a separate transaction. MSTDC is used to support SSIS transactions, so that service will need to be running on the server where you run your packages.
Along with the TransactionOption property, you’ll want to set the IsolationLevel to your desired transaction isolation level. This will define how isolated your transaction is from other transactions. Some of these should be very familiar, but there are a couple that may be new to you if you’re new to SSIS.
- ReadCommitted – Just like in the database, this prevents uncommitted data from being read. Records are locked as they are read, but the lock is immediately freed.
- RepeatableRead – Records are locked as they are read and remain locked until the transaction is committed or rolled back.
- Serializable – The entire data set being read is locked and stays locked until the transaction is committed or rolled back.
- Snapshot – Uses row versioning to ensure a point-in-time image of the data being read without locking the data.
- ReadUncommitted – No locks are deployed while the data is being read, so it’s possible to read uncommitted changes made by other transactions that may be rolled back later.
- Chaos – Just like ReadUncommitted, but during a write operation it will check for other open transactions at higher isolation levels to prevent pending changes from being overwritten.
- Unspecified – The isolation level is…unspecified.
Setting the IsolationLevel only comes into play when you’ve also set the TransactionOption to Required.
Checkpoints
If a package is performing a lot of work and fails midway through, chances are you probably won’t want to restart it from the beginning and redo all of that work. Wouldn’t it be nice if you could tell SSIS to just pick up where it left off? You can, through the use of checkpoints within your package.
To use checkpoints, the first thing you need to do is enable checkpoints within your package. To do this, edit the package properties and set the SaveCheckpoints property to True. Then set the CheckpointFileName property to a valid path and file name. This will define the checkpoint file where SSIS will track where it is in the execution process. Next, set the CheckpointUsage property to IfExists. This will tell SSIS to start from the beginning of the package if the checkpoint file doesn’t exist. However, if the file does exist, SSIS will start the package where the checkpoint file says it left off last time. If you set the CheckpointUsage property to Always and the checkpoint file doesn’t exist, your package won’t start.
At this point, the package is configured to use checkpoints. Now all you need to do is define your checkpoints. Decide which tasks should constitute a checkpoint and set the FailPackageOnFailure property of that task to True.
Additional Resources
For more information on using transactions or checkpoints in SSIS, check out the following resources:
