Post reply

Executing a DTS inside a store proc

  • April 25, 2007 at 3:50 pm

    #173745

    Hi,

    I have a DTS package (txGLBalances_WA1_PL_Annual_2006_Item_BUKRS_LSMWSel.exp) which currently takes 2 parameters (as input boxes).

    What I am looking at doing is based on the results of a query -

    SELECT     DISTINCT BUKRS, MONAT

    FROM         dswFI.dbo.txGLBalances_WA1_BS_2007_Header_LSMWSel

    loop through this query and then run the DTS package passing the results as parameters into the package.

    I believe that this is able to be achieved through the use of a StoredProc.  Can anyone assist with this dilemna.

  • April 26, 2007 at 4:28 am

    #702562

    Hi,

    You can execute the DTSRUN utility using master.dbo.xp_cmdshell passing the variables in to defined GlobalVariables.

     

    DECLARE

    @var1 INT

    DECLARE @var2 VARCHAR(10)

    DECLARE @dtsCmd VARCHAR(2000)

    SELECT

    @var1 = integerVal, @var2 = charVal

    FROM myTable

    SET

    @dtsCmd = 'DTSRun /Sservername /E /Llogfile /Npackagename /A "MyStringVar":"8"="' + @var2 + '" /A "MyIntegerVar":"3"="' + CAST(@var1 AS VARCHAR) + '"'

    EXEC

    master.dbo.xp_cmdShell @dtsCmd

    Hope this helps.


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

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

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