Conditional logic in MS SQL Server DTS packages

The intent of this post is to explain how to make conditional paths work in MS SQL Server 2000 DTS packages. You can Google for other reasources intended to help you do the same thing, but getting them to work the way I wanted took quite a bit of finagling. I wanted to be able to refer back to this the next time I have to do it.

The Setup

We’re going to use a simplified DTS package that looks like this:

A DTS package with multiple parts

As you can see, there are 3 parts of execution in this package. Let’s presume that each part takes a while to run, one after another, but might bomb out. We’d like to be able to restart the package and have it skip any of the parts that completed successfully. This will save us time on the second run, and may also be necessary to prevent duplicate data. Essentially, we’re creating a sort of resumable package.

The Structure

Don’t confuse what I’m calling “parts” with “paths”. A “path” implies a logical OR and a possibility of parallelism. While you can certainly use this technique to create dead-end paths and parallelism, this example assumes a mostly-linear sequence where we happen to just skip over a few steps when we need to.

If you’re a coder, all of this should basically look like a set of GOTO statements with each part as a block of code.

There’s nothing magical about the number of parts — you could have two or three or twenty different parts. Each part of execution needs at least two steps. If you can’t think of a second step, make it run a CHECKPOINT or a SELECT 1 or something. You’ll see why later.

The step dependencies (“constraints” in DTS terms, despite reusing a SQL word) look a little hairy, but aren’t too bad. Each part should lead to the next as usual. There should also be an extra set of paths that lead from the step that we’re going to call the “springboard”. There’s nothing magical about this step, and it can be any of the task types, but I highly recommend that you make it a sort of no-op task. Again, a CHECKPOINT or something similar is useful here.

You will also need a “landing” step. This will act something like an ELSE step. In this case it is the last step in the package, but it doesn’t have to be. You could have other things that happen after the landing step. Indeed, you could then go off and do some more conditional logic if you wanted.

HEY! YOU! PAY ATTENTION!

You need to do one more thing before you can get all of this to work. By default, when DTS creates your tasks, it doesn’t really name them what you think it names them. The task that you’ve so nicely labeled as “Initial Checkpoint” is probably actually named something like “DTSTask_DTSExecuteSQLTask_5”. So when you run the script task, you’re going to get all kinds of crazy errors about steps that it can’t find. You need to edit all of those names. This process goes much faster and is less buggy if you remove all precedence constraints, leaving only tasks. You can add them back in later.

  1. Remove all of your task constraints. Seriously. All of them. If you don’t believe me, don’t say I didn’t warn you.

  2. On the DTS menu bar, go into Package → Disconnected Edit. You can get to the same place by right-clicking in the background area of the package.

  3. Expand the Tasks and Steps trees. See all those nasty names?

  4. For each task:

    1. Select it.

    2. Look in the right window for Description, which should be the nice title you gave it, and double-click it to bring up an editor window.

    3. Copy the pretty title from the editor window and close the window.

    4. Double click the “Name” property to edit it, and replace the ugly name by pasting in the pretty title.

    5. Find the matching item in the Steps list on the left.

    6. Replace both the Name and TaskName properties with the same pretty title.

  5. Close out of the Disconnected Edit window.

  6. Add (back) in all of your task constraints.

Of course, you could actually just put the nasty names in your script. But how maintainable and readable is that? The other nice thing about renaming all of your tasks is that they now have nice names in the DTS logfiles, and debugging becomes much less horrific.

The Logic

For each part, we want to log when it finishes. We can then use this log to tell if we need to do each part again. In a nutshell:

  1. Log Start: Log the fact that we’ve started the job. DTS will log the package start for you, too, but we’re going to use our own log for another purpose.

  2. Find Skips: Fetch from the log a set of flags for which parts have completed. On the first run, it’s presumed that nothing will have been logged as completed, but on subsequent runs you may well have gotten far enough to skip some parts.

  3. Choose Path: This is the brains of our operation. It looks at the log flags you just fetched and actually modifies the DTS package to skip past what needs to be skipped.

  4. Initial Checkpoint: Our springboard.

  5. Part n Checkpoint: The first steps of each part.

  6. Log Part n Complete: Log the fact that we’ve finished the part. If the package dies later, Find Skips will let us know that we don’t need to do this part again.

  7. Final Checkpoint: Our landing zone. Anything after this is gravy. You might want another task after this that resets the log so that the next time the package starts all of the steps are executed again.

Step By Step

Log Start

On the presumption that we’re going to keep trying to run this package until it goes all the way through to the end, we’re going to need to keep track of where we are. If this is our first run through for the day, or hour, or week, or whatever timeframe you run your package on, then we’ll need to create a “LogID” — an identifier that all of the other Log steps can use to log their progress. If this is our second attempt or more, we’ll need to recognize that and fetch the latest one instead of creating a new one. Something like this might work for you if you run your package daily:

INSERT INTO Nightly_Log (When_Day, Part1, Part2, Part3, Completed)
SELECT DISTINCT DATEADD(DAY,DAY(GETDATE())-1,DATEADD(MONTH,MONTH(GETDATE())-1,DATEADD(YEAR,YEAR(GETDATE())-2007,{d '2007-01-01'}))), 0, 0, 0, 0
FROM Nightly_Log
WHERE (DATEDIFF(DAY,When_Day,GETDATE()) <= 1)
  AND (Completed IS NOT NULL)
GO
SELECT MAX(id) AS LogID
FROM Nightly_Log
WHERE (DATEDIFF(DAY,When_Day,GETDATE()) <= 1)
GO

You’ll need to use the Parameters setting in the task Properties. Go into Output Parameters and set the Parameter Type to Row Value. Use Create Global Variable to make a new package variable called LogID. Set the Type to Integer and the value to 1 for now. This step fetches the ID, and later steps will use that ID to log against.

Find Skips

This one gets a list of the steps that have completed and puts them into package variables. The SQL for this is even simpler:

SELECT Part1, Part2, Part3, Completed
FROM Nightly_Log
WHERE (id = ?)

Again, you’ll need to go into the task Parameters. This time you need to set the Input Parameter to use the LogID parameter you set up in the last step. Then go into Output Parameters and do the same thing as last time: create a package variable for each of the parts you need to check for. Alternatively, you could use a single column that holds the last completed step, then just have the one global variable. It’s really up to you. I use columns for each step, as I’m not storing integers but times, so that I can do reporting on how long it took each time. Really, it’s all up to you.

Choose Path

Visual Basic. Ugh. Here’s my script:

Option Explicit
Function Main()
	Dim oPkg, bSkip1, bSkip2, bSkip3, bSkipComplete
	If DTSGlobalVariables("Part1").Value <> 0 Then bSkip1 = True Else bSkip1 = False
	If DTSGlobalVariables("Part2").Value <> 0 Then bSkip2 = True Else bSkip2 = False
	If DTSGlobalVariables("Part3").Value <> 0 Then bSkip3 = True Else bSkip3 = False
	Set oPkg = DTSGlobalVariables.Parent
	' All steps should start off Enabled and Incomplete
	ToggleStep oPkg.Steps("Initial Checkpoint"), True, False
	ToggleStep oPkg.Steps("Part 1 Checkpoint"), True, False
	ToggleStep oPkg.Steps("Part 2 Checkpoint"), True, False
	ToggleStep oPkg.Steps("Part 3 Checkpoint"), True, False
	ToggleStep oPkg.Steps("Log Part 1 Complete"), True, False
	ToggleStep oPkg.Steps("Log Part 2 Complete"), True, False
	ToggleStep oPkg.Steps("Log Part 3 Complete"), True, False
	ToggleStep oPkg.Steps("Final Checkpoint"), True, False
	' Enable all of the step constraints, as if this is the first run-through.
	ToggleConstraint "Initial Checkpoint", oPkg.Steps("Part 1 Checkpoint"), True
	ToggleConstraint "Initial Checkpoint", oPkg.Steps("Part 2 Checkpoint"), True
	ToggleConstraint "Initial Checkpoint", oPkg.Steps("Part 3 Checkpoint"), True
	ToggleConstraint "Initial Checkpoint", oPkg.Steps("Final Checkpoint"), True
	ToggleConstraint "Log Part 1 Complete", oPkg.Steps("Part 2 Checkpoint"), True
	ToggleConstraint "Log Part 2 Complete", oPkg.Steps("Part 3 Checkpoint"), True
	ToggleConstraint "Log Part 3 Complete", oPkg.Steps("Final Checkpoint"), True
	' Now go back through and disable the parts that shouldn't be run
	If bSkip1 Then
		ToggleStep       oPkg.Steps("Part 1 Checkpoint"), True, True
		ToggleConstraint "Initial Checkpoint", oPkg.Steps("Part 1 Checkpoint"), False
		ToggleStep       oPkg.Steps("Log Part 1 Complete"), False, True
		ToggleConstraint "Log Part 1 Complete", oPkg.Steps("Part 2 Checkpoint"), False
	End If
	If bSkip2 Then
		ToggleStep       oPkg.Steps("Part 2 Checkpoint"), True, True
		ToggleConstraint "Initial Checkpoint", oPkg.Steps("Part 2 Checkpoint"), False
		ToggleStep       oPkg.Steps("Log Part 2 Complete"), False, True
		ToggleConstraint "Log Part 2 Complete", oPkg.Steps("Part 3 Checkpoint"), False
	End If
	If bSkip3 Then
		ToggleStep       oPkg.Steps("Part 3 Checkpoint"), True, True
		ToggleConstraint "Initial Checkpoint", oPkg.Steps("Part 3 Checkpoint"), False
		ToggleStep       oPkg.Steps("Log Part 3 Complete"), False, True
		ToggleConstraint "Log Part 3 Complete", oPkg.Steps("Final Checkpoint"), False
	End If
	Set oPkg = Nothing
	Main = DTSTaskExecResult_Success
End Function
Sub ToggleStep(oStep, bActive, bDone)
	oStep.DisableStep = Not bActive
	If bDone Then
		oStep.ExecutionStatus = DTSStepExecStat_Completed
	Else
		oStep.ExecutionStatus = DTSStepExecStat_Waiting
	End If
End Sub
Sub ToggleConstraint(sBefore, oAfter, bActive)
	Dim oConstraints, oConstraint
	Set oConstraints = oAfter.PrecedenceConstraints
	Set oConstraint = GetConstraint(oConstraints, sBefore)
	If bActive Then
		oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult
		oConstraint.Value = DTSStepExecResult_Success
	Else
		oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus
		oConstraint.Value = DTSStepExecStat_Inactive Or DTSStepExecStat_Waiting
	End If
	Set oConstraint = Nothing
	Set oConstraints = Nothing
End Sub
Function GetConstraint(oConstraints, sStepName)
	Dim oConstraint
	For Each oConstraint In oConstraints
		If oConstraint.StepName = sStepName Then
			Set GetConstraint = oConstraint
			Exit For
		End If
	Next
End Function

The bulk of the work is being done by those ToggleStep and ToggleConstraint functions. ToggleStep takes a step name, a boolean for whether or not the step is enabled, and a boolean for whether or not the step has been completed. ToggleConstraint is similar, taking the two steps that are involved and a boolean for whether or not the constraint is active. Skipping a part involves a series of changes:

  1. Mark the first step in the part as Enabled but also as Completed. You’re telling DTS to pretend that the first step has already run, and not to try to run it again.

  2. Mark the constraint between the springboard and the first step in the part as Wait For Completion instead of Wait for Success. Since we just marked the step as being complete, DTS will consider the constraint to be fulfilled, and won’t try to go any further into that part.

  3. Mark the last step in the part as Disabled but also as Completed. DTS won’t try to run the task, but it also won’t hold anything up.

  4. Mark the constraint between the last step in the part about to be skipped and the first step in the part after that as Wait for Completion. This ensures that when DTS looks at the first step in the next part, the constraint between the parts looks like it has been satisfied and that step can proceed as usual.

It’s a little convoluted, I know. Okay, it’s very, very convoluted. Talk to Microsoft.

Part n Checkpoint

These tasks don’t do much other than serve as placeholders for the entire series of steps for that part. DO NOT, and I am not kidding about this, use the source of a Transform Task as the first step in a part. See how I do a transform in the step after? That’s fine. Just don’t try to take out that checkpoint and use the transform source as the first step. Seriously.

Log Part n Complete

I’m sure you can guess what to do here:

UPDATE Nightly_Log
SET Partn = 1
WHERE (id = ?)

You’ll again have to set up Input Parameters, but that should be cake for you by now. Also note that you can have as many steps in the middle of the parts. You can do parallelism, or more conditional logic, or whatever you want to do — as long as you have that first and last step, and as long as each of those first and last steps only has single constraints between each other and the springboard step.

Final Checkpoint

See now why we need the landing step? It ensures that the last part is marked like it completed, whether or not that part actually ran. We can also use it to log the fact that everything has completed. And again, it doesn’t have to be the last step in the package — it’s just the step after all of the conditional logic is complete.

Summary

Conditional logic in DTS packages is doable. Once you get used to the semi-twisted methodology, it’s not even all that bad. Remembering how to do it, however, that’s the problem. All hail the Internet.

Published by

Rick Osborne

I am a web geek who has been doing this sort of thing entirely too long. I rant, I muse, I whine. That is, I am not at all atypical for my breed.