Thursday, August 23, 2012

SSIS or scripts that take a runtime parameters at the command line

If you want to run an SSIS package from the command line you use dtexec. If you have a sql script that you want to run from the command line you use sqlcmd. Both of these utilities will allow you to pass through a variable at runtime to the script/package you created. Of course there are other ways to get them to run but the command line is the lowest common denominator for really anything that runs on a Windows computer(and not just Windows, but I digress). 

SQL Script called prune.sql:
delete from Table where Column=$(Param)

and the scripting variable Param you would set like so;

C:\>sqlcmd -S "My Server" -v Param=15 -i prune.sql

SSIS package called Package.dtsx:
As an example say you have only one Control Flow object, no Data Flow objects, and the Control flow is a SQL task with variables as you see:

pruneSP is a stored proceedure that takes a parameter called @testval and when you click on Parameter Mapping you see the map from User::days_back to @testval:

Another gotcha, at least it seemed to me, was that your result set menu requires special configuration. Result Name should be set to 0 and then you will need some variable that you have created to to store the result set in. I named mine outputRS and it should be of type Object. For more on variables in SSIS see
and for a walkthrough of how to use them that is very simple try this:

Anyway once you are finished with the dtsx file, you can run it like this(from the dir that the dtsx file is in):

C:\>dtexec /FILE Package.dtsx /SET \Package.Variables[User::days_back].Properties[Value];15

No comments:

Post a Comment