You should use this option if you are using a data provider or executing objects that are not free-threaded. Examples of providers that are not free-threaded are Microsoft’s OLE DB Provider for Jet and most file-based data stores (such as Microsoft Excel, dBase, and HTML).

When a package is executed, the connection will remain open, or when accessing a file, the Win32 file system will retain the file lock until the package has completed its execution. You can use this option to close connections or release file locks when the step has completed instead of waiting for the package to finish. You might want to use this option if you transfer data to a file (destination connection) and need to email it after you complete the data dump. In that case, you would want to close the connection, freeing the file lock to send it in the email message.

Some data providers benefit from closed connections. Also, some data providers perform better with fewer connections. Much of the time, however, the performance of a package is affected adversely when you close and open connections. You should refer to the documentation on the data provider to establish the best circumstances for using this option.

You use this option if you want to stop the entire package explicitly if this specific step fails. This property was introduced in SQL Server 2000. With its addition, you can use On Failure to branch the package workflow. If you are running multiple steps in parallel, this option also could be useful for stopping the entire package.

This option, if selected, will expose OLE DB rowset data to external data consumers, such as the Query Analyzer (see Figure 6.4). When you use this option, the transformed data is returned to the calling program, not to the destination. Then you can use this feature to debug the transform before sending the data to the actual destination.

Figure 6.4. The Query Analyzer, retrieving data from the Transform Data Task when the DSO Rowset Provider option is turned on.

This option also might be helpful if you have a transform that you need to use in an application many times. Each time the call is made, the transform allows the data-transformation capabilities of the Transform Data Task to be used in many other programs.

You can access this data in two ways: querying the data by using the Transact SQL statement OPENROWSET, and joining to the package data by setting up the package as a linked server.

When you use the DSO Rowset Provider, be aware of the following:

Enable this property only if you plan to use it. After the property is enabled, the Transform Data Task for which it is set will not run when the package is executed normally. Only OPENROWSET can access the data pump task while the DSO Rowset Provider property is enabled.

You can access data from Transform Data Tasks in a package in two ways, as described in the following sections.

SELECT * FROM OPENROWSET('DTSPackageDSO','/F<pathname>','<query>') 

SELECT * FROM OPENROWSET('DTSPackageDSO', '/Usa /P /S /N<package name>','Select * ') 

sp_addlinkedserver '<servername>','<packagename>','DTSPackageDSO','<provider string>) 

SELECT i.ItemID, i.ItemName, w.Location 
FROM Inventory AS i, dtsLinkedServerName…WarehousePackageStep as w 
WHERE i.ItemID = w.ItemID 

You use this option to disable this workflow step when the package is executed. This property is extremely helpful when you are trying to debug a package.

You can set the thread priority in Windows for this step. The default is normal while low and high priorities are available.

This package is simple, because the goal is to illustrate how workflow scripting is set up and how it reacts in some situations. Although the package is simple, you should be able to apply the principles to any package in which you need to have added control of workflows.

Create a new package, and add three ActiveX script tasks. Name these tasks Step 1, Step 2, and Step 3 (see Figure 6.5).

Figure 6.5. The workflow scripting package.

Listings 6.1, 6.2, and 6.3 show the code for these tasks.

Function Main() 
   msgbox "Step 1 Running" 
   DTSGlobalVariables("attempts").Value = 0 
   Main = DTSTaskExecResult_Success 
End Function 

Function Main() 
   msgbox"Step 2 Running" 
   Main = DTSTaskExecResult_Success 
End Function 

Function Main() 
   msgbox "Step 3 Running" 
   Main = DTSTaskExecResult_Success 
End Function 

Now that the package is set up, you can begin working with the ActiveX scripts in the steps’ workflow properties.

A natural use of workflow scripting is to verify whether a file exists before the Transform Data Task loads it. The VBScript in Listing 6.4 verifies that a file called Manufacturing.mdb exists in the C:DTS2000Data directory ^[2] before it lets the step to which it is tied execute, thus allowing the rest of the package to execute.

Function Main() 
      Dim fso 
      Set fso = CreateObject("Scripting.FileSystemObject") 
      If (fso.FileExists("C:DTS2000DataManufacturing.mdb")) Then 
            Main = DTSStepScriptResult_ExecuteTask 
      Else 
            Main = DTSStepScriptResult_DontExecuteTask 
      End If 
End Function 

This code uses the File System object to verify that the Access database exists where it is supposed to exist. If this database does exist, the step executes. Otherwise, the code tells the step not to execute, and all other steps are not executed. If you have other independent workflows running, they will continue to run as expected.

This method would allow you to transform from multiple data sources without causing the package to fail if one is incorrect. To test this method, add the code in the following section to the workflow properties ActiveX script for Step 1.

Sometimes during a package execution, you need to pause until a certain condition becomes true before you can continue. The script in Listing 6.5 polls for a file, pauses five seconds, and tries again. After five attempts to find the file, the script fails. To demonstrate that fact, the file in this example does not exist.

To try this example, remove the script from the workflow properties ActiveX script in Step 1, and add the script in Listing 6.5 to the workflow properties ActiveX script in Step 2.

Function Main() 
Function Main() 
      Dim oPackage 
      Dim CurrentTime 
      Dim NextTime 
      Dim fso 

      Set fso = CreateObject("Scripting.FileSystemObject") 

      DTSGlobalVariables("attempts").Value = DTSGlobalVariables 
("attempts").Value + 1 
      CurrentTime = Now() 
      NextTime = dateadd("s", 5, Now()) 

      Set oPackage = DTSGlobalVariables.Parent 
      msgbox "Initial Value #" & DTSGlobalVariables("attempts").value 

      'Test to see if the file exists 
If (fso.FileExists("C:MyFile")) Then 
            'If it does, execute the package 
            msgbox "Execute Package" 
            Main = DTSStepScriptResult_ExecuteTask 
      Else 
            'If it doesn't, retry in increments of 5 seconds 5 times 
            If DTSGlobalVariables("attempts").Value < 6 Then 
                  do until (CurrentTime > NextTime) 
                        CurrentTime = Now() 
                  loop 
                  msgbox "Retry #" && DTSGlobalVariables("attempts").value 
                  Main = DTSStepScriptResult_RetryLater 
            Else 
                  'Stop execution once retries = 5 
                  msgbox "Execution Stopped at 5 retries" 
                  DTSGlobalVariables("attempts").Value = 0 
                  Main = DTSStepScriptResult_DontExecuteTask 
            End If 
      End If 
End Function 

As you read through the code, you will see that it uses two global variables. If the variables don’t exist, DTS will create them when they are first used.

This section illustrates how to create a loop condition in the workflow. By adding the script in Listing 6.6 to the workflow properties ActiveX script for Step 3, you will see how a loop can include more steps than the ones directly related to the calling step. (Be sure to deselect the workflow properties ActiveX script option in Step 2 before running this example.) Similar to the restart script, the loop script uses a global variable to hold an integer for counting. If the counter has not reached 5, Step 3 is not executed, and Step 1 is set to waiting, which causes Steps 1 and 2 to be executed again. (Step 2 is executed because it comes between Step 1 and Step 3 in the workflow.) The workflow will continue to loop until the counter reaches 5.

Function Main() 
   Dim oPkg 
   DTSGlobalVariables("counter").Value = DTSGlobalVariables("counter").Value + 1 
   If DTSGlobalVariables("counter").Value < 5 Then 
     Msgbox "Counter" & DTSGlobalVariables("counter").Value 
     Set oPkg = DTSGlobalVariables.Parent 
     'Set step 1 status to waiting. 
    oPkg.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus = 
       DTSStepExecStat_Waiting 
      'Do not execute task 3, step 1 will restart. 
      Main = DTSStepScriptResult_DontExecuteTask 
    Else 
      'Execute task 3 
      Main = DTSStepScriptResult_ExecuteTask 
    End If 
End Function 

For another example of looping, see Chapter 15, “Managing Distributed Databases with DTS and Message Queues.”

By using workflow scripting, you can exercise greater control of the execution of steps in a package, which allows you to validate and even prepare data for use in the tasks being executed by the steps.