Post reply

run dts package from stored procedure with parameters

  • February 15, 2008 at 10:09 am

    #69792

    Hi All

    I want to run a dts package from a stored procedure, but I want to send parameters (4 paramaters)

    is it possible to assign parameters when executing a dts package?

    if not, what is the best way to do it?

    Thanks

  • February 15, 2008 at 10:39 am

    #778925

    Yes, it's possible. You'll need to have global variables set up in your DTS package and use the DTSRUN utility to pass values to those global variables Look up "dtsrun utility" in the SQL Server books online for information on the command line switches, especially the /A argument, where you'll have to include the global variable name and a number that represents it's data type.

    From a stored procedure, you can execute xp_cmdshell to run a DTSRUN command.

    Here's an example. My global variables are named strFirstName, strLastName, and intEmployeeID

    xp_cmdshell 'DTSRUN /S "Name of your SQL Server" /N "Name of Your DTS package" /A "strFirstName:8=Bob" /A "strLastName:8=Smith" /A "intEmployeeID:22=99" /E'

Viewing 2 posts - 1 through 1 (of 1 total)

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