Command-Line Utilities In SSIS

The SSIS command-line tools are still available, but you should be using the T-SQL functions to execute and manage your packages. However, DTExec and DTUtil have been updated to work with the SSIS catalog.

So far, the bulk of Administering SSIS Topic has focused on the GUI tools you can use to administer SSIS. There is also a set of tools that can be used from a command line that serves as a Swiss Army knife to an SSIS administrator: DTExec.exe and DTUtil.exe. DTExec is a tool you use to execute your packages from a command line, and DTUtil can help you migrate a package or change the security of a package, just to name a couple of its functions. In this release of SQL Server, the command-line tools have been enhanced to support executing packages in the SSIS catalog.

DTExec

DTExec is a command-prompt tool included with SQL Server. This command is used to configure and execute SSIS packages. It gives access to all the package configuration and execution options, like connections, properties, variables, logging, and progress indicators. It also supports packages from three different sources: SQL Server databases, the SSIS package store, and the file system.

DTExecUI is a powerful tool that wraps the command-line utility DTExec. A shortcut here is to use DTExecUI to create the command for you. You can see the list of switches, minus three optional 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. This command is more verbose than is required. In reality, you only need to type the /DTS and /SERVER switches to find and execute the package.

DTExec.exe /DTS “MSDBDBSnapshots” /SERVER localhost /MAXCONCURRENT
” -1 “
/CHECKPOINTING OFF /REPORTING V
below screenshot describes three optional arguments not included in the command-line list you generated previously for DTExec. You can see these include the parameters and environments. A complete list of all the arguments can be found on Microsoft’s Tutorial Online.

DTEXEC Options

DTExecUI

In older versions of SQL Server, the primary way to execute a package was with DTExecUI.exe. With the integration of T-SQL to execute the packages, these tools will not be needed as often. This utility is a graphical wrapper for DTExec.exe, and it provides an easier way to produce the necessary switches to execute the package. You can open the utility by selecting Start ⇒ Run and typing DTExecui.exe.

Before we begin working with this utility, note that it’s a 32-bit utility. It will run on a 64-bit machine, but it will wrap the 32-bit version of DTExec.exe. In the “64-Bit Issues” section later in Administering SSIS Topic, you will learn some tricks to use to run the package in 64-bit mode.

The first screen in DTExecUI is shown in below screenshot. Here you point to the package you wish to execute and specify where the package is located. If you select the Package Store to connect to from the Package Source dropdown menu, you can view all the packages stored on the server. Notice you do not have an option for SSIS catalog. The SSIS catalog is not supported by the command-line tool DTExecUI. There are windows in SSMS for running the packages in the catalog. Your other options are SQL Server or the File System. With the SQL Server option, you will see only packages stored in the MSDB of the server that you name. The File System option allows you to point to a .dtsx file to execute.

The next page in the Execute Package Utility is the Configurations page. Here, you can select additional configuration files that you wish to include for this execution of the package. If you don’t select an additional configuration file, any configuration files already on the server will be used. You will not be able to see existing configuration files that are being used in the package.

The Command Files page provides links to files that contain a series of additional switches you can use during execution. Remember that this tool wraps DTExec, which is a command-line utility. With a command file, you can place part of the standard DTExec switches in a file and then reuse them repeatedly for every package.

                                              

Frequently Asked SSIS Interview Questions & Answers

 

The Connection Managers page shows the power of Connection Managers. This page allows you to change the Connection Manager settings at runtime to a different setting than what the developer originally intended. For example, perhaps you’d like to move the destination connection for a package to a production server instead of a QA server (see below screenshot). Another typical example is when the drive structure differs between production and development, and you need to move the Connection Manager to a different directory.

The Execution Options page (shown in below screen shot) provides advanced settings for the package execution. For example, you can force the package to fail upon the first package warning, which would normally be ignored. You can also simply validate the package without executing it. An especially powerful setting in this page is the Maximum Concurrent Executables option, which simply controls how many concurrent tasks will run in parallel. For example, it is common to migrate a package to a different environment with fewer processors, which could cause performance issues until you lower this setting. The setting of -1 means that two tasks plus the number of CPUs will run concurrently. You can use the last set of options on this page to enable checkpoints on the package, if they are not already enabled, by checking the Enable Package Checkpoints option and specifying a name.

The Reporting page (shown in below screen shot) controls the amount of detail shown in the console. The default option is Verbose, which may be too detailed for you. You may decide that you want to see only errors and warnings, for example, which allows slightly better performance than the verbose setting. You can also control which columns will be displayed in the console.

Another powerful page is the Set Values page (shown in below screenshot). This page allows you to override nearly any property you wish by typing the property path for the property. The most common use for this would be to set the value of a variable. To do this, you would use a property path that looked like this: Package.Variables[VariableName].Value. Then type the value for the variable in the next column. This page also provides a workaround for properties that can’t be set through expressions. With those properties, you generally can access them through the property path.

In the Verification page (shown in below screenshot), you can ensure that you execute only those packages that meet your criteria. For example, you may want to ensure that you execute only signed packages or packages with a certain build number. This could be handy for Sarbanes-Oxley compliance, for which you must guarantee that you don’t execute a rogue package.

The Command-Line page (shown in below screenshot) is one of the most important pages. This page shows you the exact DTExec.exe command that will be executing. You can also edit the command here. When you have the command you want, you can copy and paste it in a command prompt after the command DTExec.exe.

You can also execute the package by clicking the Execute button at any time from any page. After you click the Execute button, you will see the Package Execution Progress window, which displays any warnings, errors, and informational messages, as shown in below screen shot. In some cases, you’ll see only a fraction of the message; just hover the cursor over the text to see the full message.

DTUtil

One of the best undiscovered command-line tools in your administrator kit is DTUtil.exe. This is also a good tool for developers. It performs a number of functions, including moving packages, renumbering the PackageID, reencrypting a package, and digitally signing a package. To see everything this tool can do, 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 in SQL Server Data Tools. The next sections describe 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 applied at a workstation and user level.

Earlier in development, you can set the ProtectionLevel property to EncryptSensitiveWithUserKey (default option) to lock down password information in Connection Managers and other sensitive data. You can also set a password on the package by changing the ProtectionLevel property to EncryptSensitiveWithPassword.

By default, 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. The same would apply if you logged in with a different user account. 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.

This error message is very misleading. In truth, you can’t open the package, because the originating user encrypted it, whether intentionally or not. To fix this, the owner of the package can open it and select a different option in the Properties pane (like 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 enables SSIS designers to encrypt all packages with the default option, and when it is time to send them to production, they can develop 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 this:

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 the same batch file to reset the password to a production password. The number 3 before the word newpassword sets the ProtectionLevel property of the package to EncryptAllWithPassword.

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 regenerate the package’s XML. To do so, you can use the –I switch to generate a new PackageID and regenerate the XML, like this:

DTUtil.exe -I -File dbsnapshots.dtsx

After you do this, the package may look different when you open it because the XML has been regenerated. For example, some of your containers may be smaller than they were originally and placed in areas they weren’t originally located. You can also use this command to generate a new PackageID when you find that the developer has copied and pasted the package in SQL Server Data Tools.

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

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

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator