For those fortunate enough to have SQL 2000, using global variables
with an Execute SQL Task, or as part of the 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. Even if you are using SQL Server 2000, you
may still have a need for this due to certain syntax limitations imposed when using parameters.
In SQL Server 7.0 the only way to integrate a global variable into your SQL is to actually code
the value as a literal in the SQL statement. The simplest solution is to just build a new
statement with the value embedded in it at run-time. 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 HireDate:
' 205 (Change SourceSQLStatement)
Option Explicit
Function Main()
Dim oPkg, oDataPump, sSQLStatement
' Build new SQL Statement
sSQLStatement = "SELECT * FROM dbo.employee WHERE hire_date > '" & _
DTSGlobalVariables("HireDate").Value & "'"
' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement
' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing
Main = DTSTaskExecResult_Success
End Function