Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

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

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!


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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...