Using DTEXECUI to generate a DTEXEC command line statement the easy way

8

July 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.

DTEXECUI_CommandLine1

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.

DTEXECUI_CommandLine2

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.

DTEXECUI_CommandLine3

Next there are a handful of Execution Options you can modify. Overriding the MaxConcurrentExecutables properties for example.

DTEXECUI_CommandLine4

Another one of those I find very handy is the Set Values tab. Here you can also modify the starting value of a variable.

DTEXECUI_CommandLine5

Having made all of the changes that you want you can execute the package with the Execute button.

DTEXECUI_CommandLine6

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.

DTEXECUI_CommandLine7

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!

8 thoughts on “Using DTEXECUI to generate a DTEXEC command line statement the easy way

  1. MK says:

    I wish I had paid attention to the Command Line option a few years ago! That is super convenient. Thanks.

  2. Prabhu says:

    Thank you for command line option it was Really SUPER!!!

  3. Ravi says:

    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.

  4. Roger says:

    This was awesome! Saved me!!! Thank you

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,671 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: