DTS How to…use and return global variables from the Execute SQL or DataPump Task

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:

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.