Background

There are a number of SQL Server Data Transformation Services that are the starting point to multi-step processing. For those processes to work, it is assumed that the data downloaded from an outside source is present.

I ran into trouble when the download data was not ready on a consistent basis. Originally we had to run the processes manually, which defeated the purpose of scheduled SQL jobs. Below describes a technique where the SQL job and a DTS are used to poll the data source for an indicator, and when that indicator is found, to proceed with the processing.

Areas Covered in this Document

• Audit trails
• DTS global variables
• DTS branching
• SQL jobs

Audit Trails

I kept a log of events in a table so that performance could be documented. This measures the status of the SQL jobs and also provides a documented history.

CREATE TABLE [dbo].[EventAudit] (
[EventAuditID] [int] IDENTITY (1, 1) NOT NULL ,
[DateOccurred] [datetime] NULL ,
[EventClass] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PackageName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PackageStep] [tinyint] NULL ,
[EventNotes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]

DTS Global Variables

The ActiveX Scripting part of DTS allows you to use variables to make decisions. When you click on the Package menu of the DTS, and then Properties, the Global Variables tab opens up (Figure 1) a wide range of possibilities. In this case, I use it to hold the test value to determine if my processing can continue.

DTS Branching

The ActiveX Scripting gives a default interface like the one below:

'********************************************************************
'  Visual Basic ActiveX Script
'********************************************************************
Function Main()
Main = DTSTaskExecResult_Success
End Function

What it doesn’t mention is that there is another result that can be returned:

Main = DTSTaskExecResult_Failure

Because DTS allows three possible workflows, it allows you to direct the program flow. Keep this in mind. A lot of the process depends on this.

SQL Jobs

This job is scheduled to run from 8am to 11:30am every 15 minutes. FailureTest should return a Failure indication until it is ready to run (Figure 2). Once it is running, it will not try to run again until the job is complete.

This is an important point. FailureTest should not only test whether the data is ready to download, it should also test whether it had already ran that day.

Create the FailureTest step by scheduling the DTS through Enterprise Manager. This will create a job with the DTS name. The code for that step can be copied into this job step.

Step by Step

In summary, the process works as follows:

• Write to the log that the DTS has started
• Test the outside data source and put the results of that test in a Global Variable
• If the Test says that the download is ready, set a Success Flag and write to the log. Start the download in the next SQL job step
• If the Test says the download is not ready, set a Failure Flag, write to the log and set up a failure through a transformation that will fail.

Write to the log that the DTS has started

This is just a SQL Task that has a connection to the EventAudit table.

INSERT INTO EventAudit (
DateOccurred, 
EventClass, 
PackageName, 
PackageStep, 
EventNotes)
VALUES (getdate(), 
'DTS', 
'ReadyToGo', 
0,  
'DTS Started' )

Test the outside data source and put the results of that test in a Global Variable

This is also a SQL Task that just has some simple SQL to test whether the download is complete. You can put in T-SQL that can test for the existence of a value, or a row count threshold, or any other criteria.

selectCompletion Time
fromOutsideServer.OutsideTable
whereMarketValue = 'PortlandMarket'

What makes this SQL different is the Parameters… button (Figure 4)

Because there is only one value being returned by this SQL statement, I can map it to an Output Global Variable (Figure 5)

ActiveX Script

'**********************************************************************
'  Visual Basic ActiveX Script
'**********************************************************************
Function Main()

'MsgBox "MaxCompleteTime: " & vbcrlf & FormatDateTime( DTSGlobalVariables( "MaxCompleteTime" ).Value, VBShortDate )
'MsgBox FormatDateTime( Date, VBShortDate)
if  FormatDateTime( _
DTSGlobalVariables( "MaxCompleteTime" ).Value, VBShortDate ) = _ 
FormatDateTime( Date, VBShortDate) then
' finished process.  Is running or already ran
'  so don't run any further
Main = DTSTaskExecResult_Failure
exit function
end if
Main = DTSTaskExecResult_Success

End Function

Failure through Data Transformation Mismapping

CREATE TABLE [dbo].[FailureDest] (
[FailureDestID] [int] IDENTITY (1, 1) NOT NULL ,
[FailureColumn] [datetime] NULL 
) ON [PRIMARY]
CREATE TABLE [dbo].[FailureSource] (
[FailureSourceID] [int] IDENTITY (1, 1) NOT NULL ,
[FailureColumn] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]

One of the easiest ways for a DTS to fail is when there is a transformation from one data type to another.

To force failure, the FailureDest table was created with the important FailureColumn (tinyint) inside, so that when a character string column was transformed into it, it would fail. The Source data was FailureSource, though any table with a VARCHAR data type would work. (Figure 6)

When the initial auto mapping happens, the Transformation screen will prevent it from happening, saying there is a data mismatch. Define the FailureDest.FailureColumn initially as a VARCHAR, do the transformation mapping and then change the definition back to TINYINT.

Cautionary Note

Once the DTS reports success and the download begins, the job will not run again until it's finished. Thereafter, it will try to run again during the next 15 minutes, or whatever interval you’ve set in your SQL job. You’ll need to include another test and branching to determine if the job had already finished in the past to avoid running it again.