Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Global Variables and SQL statements in DTS
By Darren Green
Version 7.0+/2000
Level Advanced
Page 1 | Page 2

A similar method can be used to alter the Execute SQL Task:

' 205 (Change SQLStatement)
Option Explicit

Function Main()
	Dim oPkg, oExecSQL, sSQLStatement

	' Build new SQL Statement
	sSQLStatement = "INSERT dbo.NewEmployees" & vbCrLf & _
		"SELECT * FROM dbo.employee" & vbCrLf & _
		"WHERE hire_date > '" & DTSGlobalVariables("HireDate").Value & "'"

	' Get reference to the Exec SQL Task
	Set oPkg = DTSGlobalVariables.Parent
	Set oExecSQL = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask

	' Assign SQL Statement to Exec SQL Task
	oExecSQL.SQLStatement = sSQLStatement

	' Clean Up
	Set oExecSQL = Nothing
	Set oPkg = Nothing

	Main = DTSTaskExecResult_Success
End Function

To save time and reduce potential errors there is a custom task available from the Archive page which will prefix the SQL with T-SQL declares for all global variables in a package. (See Use Global Variables in Execute SQL Tasks (GVCustomTask) for more details on this custom task).

Page 1 | Page 2