Post reply

SSIS dtexec - passing variables to "Execute SQL Task"

  • August 17, 2009 at 2:32 pm

    #133916

    We currently have a duplicate SSIS package that:

    truncates sqltable1,

    populates sqltable1 from a separate database system,

    truncates sqltabel2,

    and finally copies sqltable1 to sqltable2.

    I want to be able to pass the the table names and connection information so that we can reduce the our SSIS pacakges from 12 to 1.

    To start with I created a new SSIS with a single "Execute SQL Task" and am trying to truncate a sql table.

    i have tried variations of:

    c:\dtexec /f "c:\parampass.dtsx" /SET "\package\SQLtask1.Properties[SQLStatment]";"truncate table sqltable1"

    I'm getting an error with locating the object.

    Any help would be appreciated.

    Thanks

  • August 19, 2009 at 1:24 am

    #1041299

    I was able to make this work by changing the sqltask.SqlSourceType to "Variable" and setting the SourceVariable to User::sqlstmt. Then pass the value in like this:

    c:\dtexec /f "c:\parampass.dtsx" /SET \package.variables[User::sqlstmt];"truncatetable sqltable1"

  • August 19, 2009 at 1:13 pm

    #1041805

    Woohoo!

    Thanks for the help.

    I was able to get the Execute SQL Task to work.

    Thanks again

  • August 19, 2009 at 4:06 pm

    #1041885

    No Prob - us "Forum Newbies" have to stick together. I have also seen some examples when accessing properties rather than variables, the brackets are not used. Your example would become:

    c:\dtexec /f "c:\parampass.dtsx" /SET "\package\SQLtask1.SQLStatment";"truncate table sqltable1"

    Saw that here:

    http://blogs.conchango.com/jamiethomson/archive/2004/12/13/SSIS_3A00_-Passing-parameters-on-the-command-line-to-an-SSIS-package.aspx

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

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