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!

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, SSIS Tagged: command line, microsoft sql server, SSIS