SSIS Command Line Utilities part 2: dtexec

In my previous post on dtutil, I looked at the command line utility to move and alter SSIS packages. In this post I will be looking at dtexec, the command line utility that executes packages. It allows for significant alteration of run-time options, including configured values, connections, logging and so on.

dtexec: Basic Package Execution

Like dtutil, dtexec uses a similar set of basic options to identify where the package you are operating on is located: /File, /DTS & /SQL. The eagle eyed among you will note that while the full option name is the same the abbreviated is slightly different, though their operation is the same. There is also a slight difference in the /SQL additional options – because no movement of the package is taking place, you only need to specify the /Server, /Password and /User for the package being executed (rather than separate identification of the Source and Destination package). Similarly there is a /Decrypt option for providing the password to encrypted packages. For details on this, check either my previous post or MSDN documentation.

There are two important options around basic execution. First is the /X86 option. This forces SQL Agent to to execute the package in 32-bit mode on a 64-bit server, which can be important in scenarios where 64-bit compatibility isn’t available. This commonly is an issue with drivers – however this is ignored when running from the command line. When running scenarios where you have SSIS servers in your environment running in 32 and 64-bit you probably want to read this MSDN article first: 64-bit Considerations for Integration Services.

The second is the the option to /Validate the package instead of executing it. What this does is halt the package after the validation phase – this can be useful for testing in deployment scenarios. How I sometimes use it is following deployment with a dtutil script, I run a dtexec script to validate the deployed packages, which helps catch any issues with missing / incorrect configurations etc. before you do a test run. This can be used in conjunction with the /W[arnAsError] option, which will treat a warning as an Error and cause validation to fail.

As a side note, you can place comments in the dtexec command line using the /Rem option.

dtexec: Configuring Execution

Execution can be configured using a number of options. First up is the ability to get dtexec to reference a file with all your dtexec options set in it, using the /Com[mandFile] {filespec} option. This can be handy if you are in an environment where you call dtexec via a remote tool such as Control M that has a character limit in terms of the batch command it can send.

You can provide or override the configurations used in execution using the /Conf[igFile] {filespec} to point at an XML configuration file. Similarly you can override specified connection settings using the /Conn[ection] id_or_name;{connection_string} option – multiple instances of this option are allowed and you can either point at a connection name or the GUID of the connection. You can associate an SSIS logging type with package execution using the /L[ogger] classid_orprogid;{configstring} option – this could be useful in troubleshooting if you don’t want logging enabled during normal execution but want to be able to turn it on for debug.

Individual Package properties such as variable values can be /Set – and you can set as many as is required. Finally you can set the Maximum number of executables permitted using the /M[axConcurrent] {concurrent_executables} option.

dtexec: Using Checkpoints

dtexec can make a package override its defined checkpoint usage settings – though as i’ve alluded to before, be very careful when using checkpointing as you won’t always get the expected results. But the options available are to turn /CheckPointing {on\off}, specify the actual file using /CheckFile and specify the usage of Checkpoints at package start using /Restart {deny | force | ifPossible}. These options match the settings in the package of SaveCheckpoints, CheckpointFile and CheckpointUsage.

dtexec: Execution Logging and Reporting

When running packages from dtexec, you get some additional logging options which can be useful when you are trying to do a more detailed debug on a package. Some are useful for immediate visualisation in the console window as you execute while others create file based logs.

The first console option is /ConsoleLog, which allows you to write to the console window the same entries that would normally be captured by SSIS execution logging. You can select which of these columns you want to capture, the most important of these likely to be Message. Further fine tuning in the settings for this option allow you to tighten the logging to a specific task or event – again as per standard logging. Next is /Reporting  level, which tunes what type of event to capture – Errors, Information, Warnings and so on. Usually when you want this level of reporting i’d advise using the Verbose switch to just capture everything. There is also the capability to create an event exclusion list for this option. Finally, /Sum will put a count of the number of rows the next component will receive in the log.

If you want to push all this logging out to a file (and the logs can get pretty big so they can blow out the console window pretty quickly) you can write them out using the /VLog option. If you need to venture further into debugging you can create the dump files .mdmp and .tmp using either the /DumpOnError option to dump on any error or /Dump with a specified error to only generate the files under certain circumstances. These are advanced options and to understand the output i’d advise reading the MSDN article Working with Debug Dump Files first.

dtexec: Package Verification

The dtexec utility offers a few options to verify the package you are pointing at is the one you intend to execute. First up is the option to verify the build versions using the /VerifyBuild option which allows you to tell dtexec to only run the package if it has the specified Major (and optionally Minor) build version. These are the values set at the control flow level in the SSIS package you are calling. You can check the package has a valid digital signature using /VerifySigned option. Finally are the options to match the GUID of the package you are executing using /VerifyPackageId and the version GUID of the package using the /VerifyVersionID option.

All of the options above are interesting additional protections to ensure your system is running the code that it should be, but realistically they are poor and fiddly substitutes for a proper release management process.

Why use dtexec?

Most of the options above are of course covered in SQL Agent execution options, but there are scenarios where dtexec can prove useful – most commonly where SQL Agent isn’t available or isn’t permitted to be used by IT policy. It also has value in deployment and testing scenarios. Most of the time I would advocate using SQL Agent to schedule and automate your jobs, but it’s good to have such a flexible tool available.

There is also a GUI option, dtexecui.exe which allows for the configuration of almost all the above options using a simple GUI. It also has the advantage of being able to generate the dtexec command line prompt as well, making syntax errors much more avoidable.

Official MSDN documentation can be found here for 2008 and here for 2005.