Post reply

DTS Execute SQL Task

  • June 30, 2004 at 2:51 pm

    #85723

    I have a DTS package that is creating a file based on the data that is in a table, which is working fine, but I want to update the information in the table before I execute the DTS package. So I added a "Execute SQL Task" oject that I want to execute a stored procedure that I have created. The problem that I am having is the stored procedure takes a parameter and I can not figure out how to set it. I want to be able to use a global variable that I have created in the DTS if possible. If I can some how set the value of the variable in my "Execute SQL Task" to what the global variable value is that would be great.

    Here is the code that I have in the task:

    DECLARE @CreditorID AS BIGINT

    EXECUTE usp_TP_CreateLettershopFileDetailByCreditorByCreditor @CreditorID

    Any insights?

    Thanks

  • June 30, 2004 at 7:54 pm

    #512786

    If you are using SQL 7 you cannot do (at least I believe that is right sorry, only a 2000 instance handy). If 2000 then when you create the "Execute SQL Task" object and open enter your Proc name with ? to handle the parameter position. Then press the Parameters button and associate the proper vairable to the parameters in order, the first ? with be parameter 1 the next ? will be 2 and so on.

  • July 1, 2004 at 1:47 am

    #512814

    Spot on!.

    Word of warning though - DTS sometimes gets its "knickers in a knot" when using "?" to indicate the presence of a parameter. A really annoying symptom is that DTS tells you that the code is syntactically correct without "?", you then replace parts of the code with "?" and then click the "Parameters..." button and DTS tells you there is a syntax error in the code and won't let you into the Parameters edit screen to tell it what to replace the question marks with.

    The only way around this (that I can find) is to edit the task directly in Disconnected Edit.

     

    Regards

    Jamie Thomson

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • July 1, 2004 at 7:03 am

    #512874

    There is an article on this subject here: http://www.sqldts.com/default.aspx?234

  • July 1, 2004 at 7:15 am

    #512878

    Thanks Andy & Jamie that got me what I needed. I had been trying the ? for the parameter but I couldn't get it to work. Thanks a lot.

    Kris

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply