DTS - How to pass a variable into an "Execute SQL task"

Posted on 2005-05-09
Last Modified: 2012-08-14

I am writing a DTS package that users will run from the command line.
I want them to be able to pass in a parameter at the cmd line that I can pass into a stored procedure via an Execute SQL task.
I have got my global variable setup to trap the incoming parameter and I can pass it in from the cmd line OK.  The problem is that I cant find a way of passing it into the prcoedure call.
Ultimately what I'm looking for is to be able to do this in the Execute SQL node :-
exec <procedure name> parameter1 = <DTS global variable>

How can I best do this?

Thanks in advance
Question by:meowsh
    1 Comment
    LVL 23

    Accepted Solution

    In the Execute SQL Task Properties, enter your SQL statement to execute like this:

    EXECUTE stpMyProcedure ?

    (Note the "?" - you need one question mark for each dynamic parameter, comma separated)

    Then click the Parameters... button and a box will appear with a list of parameters (Parameter 1.. etc) and you can choose the global variable from a drop down list that you want to use


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now