DTS set the SQL statement for a Step in another step

Question

Since I cannot seem to use the ? in the more complex SQL statements we are executing we read somewhere that you can set a previous step to setup the SQL for you. This is an ActiveX step which sets the steps SQL statement and substitutes in global variables.

So the SQL step has SELECT 0 in it and the step before it is essentially a VB Script as below

' 205 (Change SourceSQLStatement) Option Explicit

Function Main() Dim oPkg, oDataPump, sSQLStatement

' Build new SQL Statement
sSQLStatement = "IF (SELECT 1 FROM TABLE fm WHERE NOT EXISTS ("&_
   "SELECT HighestVersionReceived "&_
   "FROM FILE_CURRENT fc "&_
   "WHERE fc.COL = fm.COL "&_
   "AND fc.HighestVersionReceived < fm.FileVersion) "&_
   "AND [FileName] = '" & DTSGlobalVariables("GLBFileName").Value & "') = 1 "&_
   "SELECT 1 AS VALID "&_
   "ELSE "&_
   "SELECT 0 AS VALID"

' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSStep_DTSExecuteSQLTask_34").CustomTask

' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement

' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success

End Function

However it states it cannot find task with this name, it does exist. If we change it to Steps intead of Tasks it's found but the Step doesn't have a SourceSQLStatement property.

So i'm out of my depth. Any ideas?

Probably change the type of step that runs the SQL?

Answer 1

Have you tried changing the complex TSQL statement to a stored procedure?

You could then have an input parameter in the stored procedure for FileName. This stored procedure could be used as the source of your data pump task, with the parameter assigned the value of the DTS global variable at run-time by the "?" syntax you mention.