Results 1 to 2 of 2

Thread: DTS question

  1. #1
    Join Date
    Sep 2003

    Unanswered: DTS question

    Is there a way to use Global variables in the 'Execute SQL task' in a DTS package? if no is there a way to?

  2. #2
    Join Date
    Aug 2003
    Yes, depends on what version of SQL Server you have. In SQL Server 2000, you can use question marks as place holders for parameters, then assign the global variables to the question marks using the parameters button in the execute sql screen.

    If you are using sql7 then I found this to work. Add an active x script to the package that creates and sets the sql command of the execute sql task. when you create the sql command in the vb script, use the variables you want. Here is the syntax of the active x script.


    ' Visual Basic ActiveX Script

    Function Main()

    Dim oPkg, oExecSQL, sSQLStatement

    sSQLStatement = "EXEC stpr_createtable " & DTSGlobalVariables("tableName").Value

    Set oPkg = DTSGlobalVariables.Parent
    Set oExecSQL = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTa sk

    oExecSQL.SQLStatement = sSQLStatement

    Set oExecSQL = Nothing
    Set oPkg = Nothing

    Main = DTSTaskExecResult_Success
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts