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