8 How to use and return global variables from the Execute SQL or DataPump Task
For those fortunate enough to have SQL 2000, using and returning global variables
with an Execute SQL Task, or using them as part of source statement in the DataPump
task is now a standard feature. For those still using SQL Server 7.0 this tedious process,
but here are some examples of how it can be done.
The only way to integrate a global variable into your SQL is to do some form of dynamic
amendment of the statement at runtime. The simplest solution is to just build a new
statement with the value embedded in it. You can do this from an ActiveX Script Task
as illustrated below.
This example amends the SELECT statement for a DataPump task, to filter based on
the value of the global variable FilterDate:
Option Explicit Function Main() Dim oPkg, oDataPump, sSQLStatement ' Get Package Object Set oPkg = DTSGlobalVariables.Parent ' Get DataPump CustomTask Set oDataPump = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask ' Build new SQL Statement sSQLStatement = "SELECT * FROM employee WHERE hire_date > '" & _ DTSGlobalVariables("FilterDate").Value & "'" ' Assign SQL Statement to Source of DataPump oDataPump.SourceSQLStatement = sSQLStatement ' Clean Up Set oDataPump = Nothing Set oPkg = Nothing Main = DTSTaskExecResult_Success End Function
A similar method can be used to alter the Execute SQL Task:
Option Explicit Function Main() Dim oPkg, oExecSQL, sSQLStatement ' Get Package Object Set oPkg = DTSGlobalVariables.Parent ' Get Exec SQL CustomTask Set oExecSQL = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask ' Build new SQL Statement sSQLStatement = "DELETE employee WHERE hire_date < '" & _ DTSGlobalVariables("FilterDate").Value & "'" ' Assign SQL Statement Exec SQL Task oExecSQL.SQLStatement = sSQLStatement ' Clean Up Set oExecSQL = Nothing Set oPkg = Nothing Main = DTSTaskExecResult_Success End Function
For a more details explanation of dynamically altering tasks, please see How to dynamically change tasks.
To save time and reduce potential errors I wrote a custom task which offers
a slightly different method of using global variables in Execute SQL Tasks, GVCustomTask.
Returning a value from SQL for use in a global variable is not quite as simple.
One method is to use an ActiveX Script Task and an ADO query to get the result which can be assigned to the global variable in the script.
The main drawback of this method is it cannot use an existing DTS connection,
so all connection parameters have to be coded into the ActiveX Script or stored in
another global variable.
An alternative method uses the DataPump and an ActiveX Script
Transformation to set the global variable. Starting with a blank design sheet,
follow these steps to produce a sample package:
- Add two SQL connections pointing to your local SQL Server, and set the database to be pubs
- Add a DataPump task between the two connections
- Set the DataPump source to SELECT MAX(hire_date) AS hire_date FROM dbo.employee
- Set the Destination to [pubs].[dbo].[employee]
- Add a single ActiveX Transformation between hire_date and a destination column of your choice, with the following code:
Function Main() DTSGlobalVariables("FilterDate").Value = DTSSource("hire_date") Main = DTSTransformStat_SkipRow End Function
This can be ammended to return any value, even system functions, e.g. SELECT SUSER_SNAME() AS [USER_NAME].
The transformation result DTSTransformStat_SkipRow is important
as it prevents the DataPump actually trying to perform any insert or data type validation.
This means that you can choose any table as your destination and indeed any column,
without the transformation failing.