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:
<SQL CODE HERE>
delete from Table where Column=$(Param)
<SQL CODE HERE>

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 
http://www.simple-talk.com/sql/ssis/working-with-variables-in-sql-server-integration-services/
and for a walkthrough of how to use them that is very simple try this:
http://sqlfool.com/2009/08/getting-started-with-variables-in-ssis/

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