Using DTEXECUI to generate a DTEXEC command line statement the easy way
8July 31, 2013 by Kenneth Fisher
SSIS is one of those tools that I love and hate at the same time. It’s a great ETL tool but to be honest it feels like it was written not just one committee but several different ones. And I won’t say they weren’t talking to each other (I kind of wish they didn’t) since I’m fairly certain they were actively fighting with each other. The individual pieces are great; they just don’t seem to work well with each other. Data types for example, there are three different sets, and in a system that is heavily typed it can get seriously confusing.
One of the tools that I really like however is DTEXECUI, which may look familiar if you remember DTSRUNUI. DTEXECUI otherwise known as “Execute Package Utility” is a user interface for running an SSIS package. I’m sure someone out there is thinking, “No you have it wrong, it’s DTEXEC.” Well DTEXEC is the command line execution utility and DTEXECUI is an execution utility with a GUI.
Here is what it looks like.
Note that you can select a Package source and Package, and if you need to connect to a server to get your package the connection options are there also.
Once you have selected a package you have a number of options.
I’m only going to demonstrate a few of them right now but you can look up DTEXECUI in BOL to get a full description of the rest. 2005, 2008, 2008 R2, 2012 I’m using 2008 R2.
Note: I’m using a simple package with two connection managers and a package variable.
First of the options I’m going to discuss and one of the ones I find most useful is “Connection Managers”. From here you can change the connection string for any connection manager. Simply check the checkbox next to the Connection Managers name and then modify the Connection String property.
Next there are a handful of Execution Options you can modify. Overriding the MaxConcurrentExecutables properties for example.
Another one of those I find very handy is the Set Values tab. Here you can also modify the starting value of a variable.
Having made all of the changes that you want you can execute the package with the Execute button.
Or my all time favorite, and the purpose for this particular post, you can pull the command line statement required for all of the changes you have made.
Copy and paste the command listed in front of DTEXEC.exe and you have a command ready to execute the SSIS package with your changes.
DTEXEC.exe /FILE “C:\SSIS Projects\Temporary Workspace\Temporary Workspace\Package1.dtsx” /CONNECTION “Flat File Connection”;”C:\NewLocation.txt” /MAXCONCURRENT 3 /CHECKPOINTING OFF /REPORTING EW /SET “\Package.Variables[MyVariable].Value”;1
Note the /CONNECTION, /MAXCONCURRENT and /SET options for the DTEXEC.exe command. These represent the changes I made in the various tabs. And wasn’t that a lot easier than trying to generate the command from scratch!
I wish I had paid attention to the Command Line option a few years ago! That is super convenient. Thanks.
It is rather handy isn’t it. Glad you like it.
Thank you for command line option it was Really SUPER!!!
Glad to hear it. It’s certainly the easiest way I’ve found so far.
Can we get command line syntax for packages deployed in SSISDB using environment references and parameters.
I would assume so. You should be able to tell the latest GUI where the package is, and modify the params & references etc then get the command line syntax. I haven’t tried it myself but based on purpose of the GUI I can’t imagine it wouldn’t work.
This was awesome! Saved me!!! Thank you
Glad to hear it 🙂