Using ActiveX to Assign Global Variables in DTS Packages

Dynamic Properties Task are great for simple DTS packages, however, if you have ever used DTS packages extensively then you will know that Dynamic Properties Tasks are inflexible when dealing with objects like Transform Data Tasks.

For example, if you had a Transform Data Task that copies records from one table to another within the same database (see example below) then you can certainly use a Dynamic Properties Task to set the Database Connection Properties for both connections with values like server name, database name, user and password.

However, when it comes to the Transform Data Task, the Source and Destination table properties also requires updating. If you use a Dynamic Properties Task, then you will need to specify and set up two new variables (strings) for Source and Destination which looks like [database].[dbo].[table]
If you have several Transform Data Tasks in your DTS package then your Dynamic Properties Task gets quite busy. Also, if you use Global Variables to pass this data into the Dynamic Properties Task then this too becomes hard to manage. This is because Dynamic Properties Tasks do not allow you to compute variables like the database name from just one Global Variable, instead you have to specify things like database name multiple times for every Transform Data Task.

If all you really want is somewhere to allow you to easily change things like server name, database name, user/password, input/output file, etc. without the hassle of changing numerous Global Variables or manually changing them via the Disconnected Edit facility then a good alternative is to use an ActiveX Script Task.

In the following example, the DTS package has a Transform Data Task that backs up employee records from one table (called Employee) to another (called Employee_Backup) within the same customer information database (CID). It then loads employee data from a CSV file into the Employee table and runs an update process.

Here are the steps to utilize an ActiveX Script Task to apply values stored in Global Variables:

To begin, add/review your Global Variables.
You will not require the Dynamic Properties Task, so remove it.
In place of the Dynamic Properties Task add an ActiveX Script Task.

In the ActiveX Script Task, paste the following script.
(This script applies only to this example, so you will need to modify this to suit your requirements.)

'************************************************************************
' Apply Global Variables - Visual Basic ActiveX Script
' Written by John Buoro
' The script will go through and update various object properties with 
' values obtained from Global Variables.
'************************************************************************

Function Main()

Dim sSourceFile
Dim sServerName
Dim sDatabase
Dim sUserName
Dim sPassword
Dim Debug
Dim FoundError
Dim oPackage
Dim oConnection
Dim oTask

'Initial settings.
Debug = False
FoundError = False

'Get Global Variables.
sSourceFile = DTSGlobalVariables("SourceFile").Value
sServerName = DTSGlobalVariables("ServerName").Value
sDatabase = DTSGlobalVariables("Database").Value
sUserName = DTSGlobalVariables("UserName").Value
sPassword = DTSGlobalVariables("Password").Value
If Debug Then MsgBox( sSourceFile + " " + sServerName + 
	" " + sDatabase + " " + sUserName + " " + sPassword)

'Test Global Variables.
If Len(sSourceFile) = 0 Then FoundError = True
If Len(sServerName) = 0 Then FoundError = True
If Len(sDatabase) = 0 Then FoundError = True
If Len(sUserName) = 0 Then FoundError = True
If Len(sPassword) = 0 Then FoundError = True
If FoundError Then
	If Debug Then MsgBox( "FoundError = True" )
	Main = DTSTaskExecResult_Failure
	Exit Function
End If

'Get a handle to the Package object.
Set oPackage = DTSGlobalVariables.Parent

'Update "Text File (Source)"
Set oConnection = oPackage.Connections("Text File (Source)")
oConnection.DataSource = sSourceFile

'Update "CID Database A"
Set oConnection = oPackage.Connections("CID Database A")
oConnection.DataSource = sServerName
oConnection.Catalog = sDatabase
oConnection.UserID = sUserName
oConnection.Password = sPassword

'Update "CID Database B"
Set oConnection = oPackage.Connections("CID Database B")
oConnection.DataSource = sServerName
oConnection.Catalog = sDatabase
oConnection.UserID = sUserName
oConnection.Password = sPassword

'Update "CID Database C"
Set oConnection = oPackage.Connections("CID Database C")
oConnection.DataSource = sServerName
oConnection.Catalog = sDatabase
oConnection.UserID = sUserName
oConnection.Password = sPassword

'Update DTSTask_DTSDataPumpTask_1
Set oTask = oPackage.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
oTask.SourceObjectName = sSourceFile
oTask.DestinationObjectName = "[" & sDatabase & "].[dbo].[Employees]"

'Update DTSTask_DTSDataPumpTask_3
Set oTask = oPackage.Tasks("DTSTask_DTSDataPumpTask_3").CustomTask
oTask.SourceObjectName = "[" & sDatabase & "].[dbo].[Employees]"
oTask.DestinationObjectName = "[" & sDatabase & "].[dbo].[Employees_Backup]"

'Clean up.
Set oTask = Nothing
Set oConnection = Nothing
Set oPackage = Nothing
Main = DTSTaskExecResult_Success

End Function

The script will go through and update various object properties with values obtained from Global Variables.
A good way to identify what the property names are called is to use the Disconnected Edit functionality located under the Package menu.
The "Get Global Variables" section of the code retrieves the values entered in the Global Variables.
When changing this to suit your requirements, make sure that these match your Global Variables.
The same also applies to the "Test Global Variables" section of the code.
The Update "Text File (Source)" section populates the Connection Properties identified by the title "Text File (Source)" with the value assigned in the Global Variable called SourceFile.
```
'Update "Text File (Source)"
Set oConnection = oPackage.Connections("Text File (Source)")
oConnection.DataSource = sSourceFile
```
In Disconnected Edit, the Connections section contains the connection called "Text File (Source)"
The property name we want to change is called DataSource as shown below.
Next we will need to ensure that the three database connections (A, B and C) have their properties updated to point to the correct database on the correct server.
In this example the database is SampleDB which is located on server SQLTEST.
The connection properties for "CID Database A" is assigned the Global Variable values for ServerName, Database, UserName and Password.
```
'Update "CID Database A"
Set oConnection = oPackage.Connections("CID Database A")
oConnection.DataSource = sServerName
oConnection.Catalog = sDatabase
oConnection.UserID = sUserName
oConnection.Password = sPassword
```
The Disconnected Edit shows that property names are DataSource, Catalog, UserID and Password.
Next, there are two DTS DataPump Tasks that require updating because the CSV file has changed and the database name has changed.
```
'Update DTSTask_DTSDataPumpTask_1
Set oTask = oPackage.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
oTask.SourceObjectName = sSourceFile
oTask.DestinationObjectName = "[" & sDatabase & "].[dbo].[Employees]"
```
The Disconnected Edit shows the task called "DTSTask_DTSDataPumpTask_1" where the property name SourceObjectName is set to the Global Variable called SourceFile.

Also, the database name is updated so that the property name DestinationObjectName contains the database name as well as the table name "Employees".
The other DTS DataPump Task (DTSTask_DTSDataPumpTask_3) is also updated so that the database name is correct. Specifically, the property name SourceObjectName contains the database name and the table name "Employees".
The DestinationObjectName contains the database name and the table "Employees_Backup".
```
'Update DTSTask_DTSDataPumpTask_3
Set oTask = oPackage.Tasks("DTSTask_DTSDataPumpTask_3").CustomTask
oTask.SourceObjectName = "[" & sDatabase & "].[dbo].[Employees]"
oTask.DestinationObjectName = "[" & sDatabase & "].[dbo].[Employees_Backup]"
```

We're done. Just make sure that the ActiveX Script Task is set to run at the start of the workflow.

You can even pass values into the Global Variables and hence be used within the DTS packages by passing them as parameters when running or scheduling DTSRun.
The example below has changes to all the Global Variables, however, you only need to provide the parameter that is different to the Global Variables specified in the DTS package.
See the online help for more information on DTSRun, specifically the /A command-line parameter.

dtsrun /S sqlprod /N "CID Employee Import" /A SourceFile:8="E:\Data\Emp.csv" /A ServerName:8="SQLPROD" /A Database:8="CID_DB" /A UserName:8="CIDDBAdmin" /A Password:8="xxxxxx"