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.

preferenceswindow 

Choose Package Configurations.  Your presented with this window.

configwindow

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.

xmlconfigwindow

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

variablevalue

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. 

xmlpath

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.

Advertisements

One response to “Using DTEXEC and /Set to run SSIS packages

  1. the problem of data corruption in the files of specified format can be fixed by the restore company web with mdf file utility

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s