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
- 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.)
- The script will go
through and update various object properties with values obtained from
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
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.
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. 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.
The Disconnected Edit shows the task called "DTSTask_DTSDataPumpTask_1"
where the property name SourceObjectName is set to the Global Variable
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.
property name SourceObjectName contains the database name and the table
The DestinationObjectName contains the database name and the table "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.