Using DTEXEC and /Set to run SSIS packages

I have not had  much of a need to run SSIS packages from the command line as most of my packages run in SQL Agent just fine.  I’ve written a SSIS package that I have to call many times so I can generate some load and the easiest way I could see to do this was just using a command line to run of it.  This was a very quick package I created and had some variables that I wanted to change the value to quickly and easily on each run so I could run the packages at different values. 

I looked online for the /set command and found Jamie Thomson’s(Blog|Twitter) blog about the command located here. 

This helped out a lot but was still having issues with the syntax coming out correctly the very last comment had a great suggestion to simply create a config file that you could then go read to get the full path.  That worked so well I figured I would document it for next time. 

How to get your path to your variable for your /set command

Open your package and go into Package Configurations.  You can do this by right mouse clicking on the open space on your Control flow you should see this menu.

 

Choose Package Configurations.  Your presented with this window.

Mark the Enable Package Configurations and Add a new configuration.

Choose XML file on the next window and place the file in a simple path easy to remember.

On the next screen choose the variable you want and mark the Value box

Go ahead and click Next then Finish. 

Now you can open this file and see the full path and exactly what the DTEXEC command needs. 

In my case it reads “\Package.Variables[User::iCounterEnd].Properties[Value]"

Now my DTEXEC command looks like this

DTExec.exe /set \Package.Variables[User::iCounterEnd].Properties[Value];1 /File "c:\package.dtsx”

This is allowing me to set the package to only run through 1 loop instead of whatever I had set in the package.  Make sure you go back to your package and disable/remove the package configuration you just created or it will keep using the xml file. 

This has made it really easy to do my testing so hopefully you can use this as well in the future and it will be a little less confusing than it was for me.