4 How to dynamically change...Tasks
I would recommend familiarising yourself with ScriptPkg before reading this article:
Dynamically change package properties utilising ScriptPkg
This article will focus on the more complex process of changing Task properties, but
for an explanation and examples of changing connection properties please see:
How to dynamically change...Connections
On with the article..
Each package contains a collection of Tasks, which not surprisingly contains Task objects.
These tasks objects contain a CustomTask, which is where the interesting properties are stored.
For example the Execute SQL Task contains a Custom Task (DTSExecuteSQLTask) with
the following properties:
oCustomTask.Name
oCustomTask.Description
oCustomTask.SQLStatement
oCustomTask.ConnectionID
oCustomTask.CommandTimeout
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:
Description |
CustomTaskID |
Active Script Task | DTSActiveScriptTask |
Execute Process Task | DTSCreateProcessTask |
Execute SQL Task | DTSExecuteSQLTask |
Data Driven Query Task | DTSDataDrivenQueryTask |
Transfer SQL Server Objects | DTSTransferObjectsTask |
Send Mail | DTSSendMailTask |
Bulk Insert Task | DTSBulkInsertTask |
Transform Data Task | DTSDataPumpTask |
OLAP Services Processing Task | DTSOlapProcess.Task |
Padding Task | PaddingTask.Padding |
GVCustomTask | GVCustomTask.GlobalVariableToSQL |
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:\NewPath\NewFile.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:\NewPath\NewFile.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:\NewPath\NewFile.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
|