Command Line Utilities

Thus far, the discussion in this chapter has focused on the GUI tools you can use to administer SSIS. There are also a series of tools that you can use from a command line that act as a Swiss Army knife for an SSIS administrator. The two main tools that you’ll use are DTExec.exe and DTUtil.exe.

DTExec.exe is a tool you’ll use to execute your packages from a command line, and Dtutil.exe can help you migrate a package or change the security of a package, just to name a few of its functions.

DTExec.exe

You’ve already seen the power of DTExecUI.exe for executing your packages. That tool wraps the command line utility DTExec.exe. A shortcut here is to use DTExecUI.exe to create the command for you. You can see the full list of switches for this utility by typing the following:

 dtexec.exe /?

For example, to execute a package that is stored in the msdb database on your localhost, you could use the following command:

 DTExec.exe /DTS "\MSDB\DBSnapshots" /SERVER localhost /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING V

Tip 

This command is more verbose than is required. In reality, you must only type the /DTS and /SERVER switches to find and execute the package.

DTUtil.exe

One of the best undiscovered command line tools in your administrator kit is DTUtil.exe. This is also a good tool for developers as well. The tool performs a number of functions, including moving packages, renumbering the PackageID, re-encrypting a package, and digitally signing a package. To see everything this tool can do, you can type the following command from a command prompt:

 DTUtil.exe /?

Essentially, this tool can be used to do many of the things that you do in Management Studio and, to a lesser extent, BIDS. The next few sections show you a few creative ways to use DTUtil.exe.

Re-Encrypting All Packages in a Directory

By default, SSIS files in development are encrypted to prevent an unauthorized person from seeing your SSIS package. The type of encryption is seamless behind the scenes, and is at a workstation and user level. So, if you were to send a package that you’re developing to another developer on your team, he or she would not be able to open it by default. The same would apply if you logged in with a different user name. You would receive the following error:

 There were errors while the package was being loaded. The package might be corrupted. See the Error List for details.

The error is very misleading. In truth, you can’t open the package because the originating user encrypted the package whether on purpose or not. To fix this, the owner of the package can open the package and, in the Properties pane, select a different option (such as a package password) for the ProtectionLevel option. The default option is EncryptSensitiveWithUserKey. To protect the entire package with a password, select the EncryptAllWithPassword option.

Another useful option is when an SSIS designer encrypts all packages with the default option, and when he or she is ready to send it to production, he or she develops a batch file to loop through a directory’s .dtsx file and set a password. The batch file would use DTUtil.exe and look like the following:

 for %%f in (*.dtsx) do Dtutil.exe /file %%f /encrypt file;%%f;3;newpassword

This would loop through each .dtsx file in your directory and assign the password of newpassword. The production support group could then use a similar batch file to first decrypt and then reset the password to a production password. If you run the preceding command directly from the command line and not via a batch file, then replace all the %%f tokens with %f, and the same goal is accomplished.

Handling a Corrupt Package

Occasionally, when you copy objects in and out of a container, you may corrupt a given task in the package. In that case, you can’t delete the task, or move it outside the container, or link it in the container. This doesn’t happen often, but when you suspect you have a corrupt package or object, you can use DTUtil.exe to re-generate the package’s XML. To do this, you can use the –I switch to generate a new PackageID and regenerate the XML, as shown here:

 DTUtil.exe -I -File dbsnapshots.dtsx

When you do this, the package may look different when you open it, since the XML has been regenerated. For example, some of your containers may be smaller than original, and placed in areas they weren’t originally in. You can also use this command to regenerate the PackageID when the developer copies and pastes the command.

You can also create a batch file to loop through the directory and re-generate the ID for every package in the directory. The batch file will loop through every .dtsx file and execute DTUtil.exe. The batch file would look like this:

 for %%f in (*.dtsx) do dtutil.exe /I /FILE "%%f"