DTS How to…dynamically change…Tasks

In this case, perhaps the most interesting is the SQLStatement property, which holds the SQL query
as seen in the DTS Designer. The easiest way to investigate the properties of a CustomTask is to
build a package with the relevant task, and examine the
ScriptPkg output. I have also written a simple utility that lists the CustomTask properties,
DTSObject, which is very quick and easy to use.

To gain reference to a CustomTask you need to know the CustomTaskID. This is a property of the Task object
and identifies the type of CustomTask contained by the Task.
These are listed below for convenience:

N.B. All of the examples below are written in ActiveX Script (VBScript),
and are designed for use within an Active Script Task. They will of course
function equally well in Visual basic with a few minor tweaks.

The following example examines a package, looking for an Execute Process Task.
It will then update the ProcessCommandLine property, the file to execute, to a new value:

Option Explicit
Function Main()
    ' Declare Variables
    Dim oPKG
    Dim oTask
    Dim oCustomTask
    'Get Package Object
    Set oPKG = DTSGlobalVariables.Parent
    'Examine Tasks
    For Each oTask in oPKG.Tasks
        'Test for Execute Process Task
        If oTask.CustomTaskID = "DTSCreateProcessTask" Then
            'Get CustomTask (Execute Process Task) Object
            Set oCustomTask = oTask.CustomTask
            'Set new ProcessCommandLine value
            oCustomTask.Properties.Item("ProcessCommandLine").Value =_
              "C:NewPathNewFile.exe"
        End If
    Next
    ' Clear Up
    Set oCustomTask = Nothing
    Set oTask = Nothing
    Set oPKG = Nothing

    Main = DTSTaskExecResult_Success
End Function

The one draw back with the example above is that it will amend every Execute Process Task.
To amend a particular Execute Process Task, you will need to expand the Test line
to include an additional condition. The two properties we could use are Name and Description.
Name is automatically assigned by the designer (DTSTask_CustomTaskID_Count), and although it is defined as Read/Write in Books Online,
for all of the Microsoft defined Tasks it can only be set and read via code. Description on the other hand is set via
the Properties sheet of the Task in the Designer. This does of course mean that you
must ensure the Description is unique for each task
or at least unique to the group of tasks that you wish to be amended.

To test for a specific instance(s) of a Task with a Description of “ExecProcTask Number 1”
, expand the Test line as detailed below:

Option Explicit
Function Main()
...
'Test for Execute Process Task (Specific Description)
        If oTask.CustomTaskID = "DTSCreateProcessTask" _
          And oTask.Description = "ExecProcTask Number 1" Then

OR
'Test for Execute Process Task (Specific Description)
        If oTask.CustomTaskID = "DTSCreateProcessTask" _
          And oTask.CustomTask.Description = "ExecProcTask Number 1" Then
...
End If
    Next

    Main = DTSTaskExecResult_Success
End Function

If you do know the name of the task, then you can reference it directly.
The two examples below both demonstrate this, but using different methods
to gain reference to the ProcessCommandLine property:

Option Explicit
Function Main()
    ' Declare Variables
    Dim oPKG
    Dim oTask
    Dim oCustomTask
    'Get Package Object
    Set oPKG = DTSGlobalVariables.Parent
    ' Get the CustomTask for "DTSTask_DTSCreateProcessTask_1"
    Set oCustomTask = oPKG.Tasks("DTSTask_DTSCreateProcessTask_1").CustomTask
    ' Set new ProcessCommandLine value 
    oCustomTask.Properties.Item("ProcessCommandLine").Value =_
      "C:NewPathNewFile.exe"
    ' Clear Up 
    Set oCustomTask = Nothing 
    Set oTask = Nothing 
    Set oPKG = Nothing 

    Main = DTSTaskExecResult_Success
End Function

OR

Option Explicit
Function Main()
    ' Declare Variables
    Dim oPKG
    Dim oTask
    Dim oCustomTask
    'Get Package Object
    Set oPKG = DTSGlobalVariables.Parent
    ' Set new ProcessCommandLine value for "DTSTask_DTSCreateProcessTask_1"
    oPKG.Tasks("DTSTask_DTSCreateProcessTask_1").CustomTask.Properties._
      Item("ProcessCommandLine").Value = "C:NewPathNewFile.exe"
    ' Clear Up 
    Set oPKG = Nothing 

    Main = DTSTaskExecResult_Success
End Function

An obvious extension of this would be to set the property to value held elsewhere. My
Text File Connection file name article demonstrates methods
for GlobalVariables (DTS) and values held in a SQL table.

The following example, just for the sake of another example, will change the SQLStatement of specified
Execute SQL Task to the contents of a file.

(This example uses the Microsoft FileSystemObject)

Option Explicit
Function Main()
    ' Declare Variables, Constants & Objects
    Dim oPKG
    Dim oTask
    Dim oCustomTask
    Dim oFS
    Dim oFile
    Const ForReading = 1
    Set oFS = CreateObject("Scripting.FileSystemObject")

    'Get Package Object
    Set oPKG = DTSGlobalVariables.Parent
    'Examine Tasks
    For Each oTask in oPKG.Tasks
        'Test for Execute SQL Task (Specific Description)
        If oTask.CustomTask.Description = "Execute SQL Task: FileDemo" _
          And oTask.CustomTaskID = "DTSExecuteSQLTask" Then
            'Get CustomTask (Execute SQL Task: FileDemo) Object
            Set oCustomTask = oTask.CustomTask
            'Open SQL File
            Set oFile = oFS.OpenTextFile("C:DemoSQL.sql", ForReading)
            'Set SQLStatement to SQL File data
            oCustomTask.Properties("SQLStatement").Value = oFile.ReadAll
        End If
    Next

    ' Clear Up
    Set oCustomTask = Nothing
    Set oTask = Nothing
    Set oPKG = Nothing

    Main = DTSTaskExecResult_Success
End Function