Executing SSIS Packages Using DTExec

Applying programming principles to the structure of SQL Server 2008 Integration Services packages allows you to increase their flexibility and simplify their maintenance. Replacing hard coded values with variables and referencing them through configurations minimizes the amount of effort involved in deploying the same code under varying circumstances, where the location of data or factors affecting its transformations change. One way of dealing with such changes takes advantage of the capabilities of the DTExec and DTExecUI utilities, which give you an option to assign values to arbitrary properties of components within an SSIS package when invoking its execution.

As we have demonstrated in our recent articles, applying programming principles to the structure of SQL Server 2008 Integration Services packages allows you to increase their flexibility and simplify their maintenance. Replacing hard coded values with variables and referencing them through configurations minimizes the amount of effort involved in deploying the same code under varying circumstances, where the location of data or factors affecting its transformations change. In particular, one way to deal with such changes, which we have already explored in detail, involves modifying XML files, registry entries, SQL server tables, environment variables, or variables in parent packages that store internal configuration settings of SSIS packages. Another one, which will be presented here, takes advantage of the capabilities of the DTExec and DTExecUI utilities, which give you an option to assign values to arbitrary properties of components within an SSIS package when invoking its execution. (Note that either of these two methods eliminates the need to edit package content with Business Intelligence Development Studio).

Before we focus on our main subject, we first need to describe the notation used to reference properties of individual SSIS package objects, known as the property path. If you have read our previous article, its syntax should be (at least partially) already familiar to you, since such paths are generated automatically when exporting properties to package configurations with Package Configuration Wizard. In case you have not read it, in that particular case our goal was to extract a subset of rows from the vEmployee view in the AdventureWorks database (which we referenced by creating srcServer and srcDatabase variables with the package scope and residing in the User namespace), based on the match on the City field (represented by the City variable, sharing the same User namespace but scoped on the Data Flow Task level). The first two of these variables were included in the ConnectionString property of the OLE DB Connection Manager, while the third one was used in Conditional Split Transformation, which redirected rows matching our criteria to a designated output. Similarly, we used the dstFilePath User variable to specify the location and name of the destination file (by incorporating it into the ConnectionString property of the Flat File Connection Manager).

In general, in order to reference a variable via the property path notation, you would need to provide its scope, namespace, and name. Regardless of individual differences, the notation always starts with the \Package prefix. The backslash character ('\') is a container separator, period ('.') designates collections (such as Properties and Variables), while members of these collections are identified by their index, enclosed in square brackets ([]). The index is formed by specifying a unique characteristic of a collection member, which can be either just its name (in the case of Properties) or (when dealing with Variables, where duplicates are allowed) also the namespace, with the double colon (::) separating the two.

The best way to get familiar with this syntax is to review some examples. In regard to our sample package, its four variables (srcServer, srcDatabase, dstFilePath, and City) would be represented by the following four property paths. (Note that in each case, we are referring to their individual values, which is the reason for inclusion of the [Value] element):

\Package.Variables[User::srcServer].Properties[Value]
\Package.Variables[User::srcDatabase].Properties[Value]
\Package.Variables[User::dstFilePath].Properties[Value]
\Package\Data Flow Task.Variables[User::City].Properties[Value]

It is important to note that it is also possible to use property paths in order to reference properties of package components directly (rather than through variables). Let's review how this can be accomplished with our sample package. Its content consists of five components - OLE DB Connection Manager with the corresponding OLE DB Source providing access to the data source, Conditional Split Transformation redirecting rows matching our criteria to a designated output, and Flat File Connection Manager with its Flat File Destination where the results were stored. If we wanted to provide direct access to their relevant properties (which include ConnectionString for our two connection managers and FriendlyExpression used in the conditional split), we would need to specify:

\Package.Connections[OLE DB Connection Manager].Properties[ConnectionString]
\Package.Connections[Flat File Connection Manager].Properties[ConnectionString]
\Package\Data Flow Task.Properties[[Conditional Split Transformation][City][FriendlyExpression]]

Now let's take a look how we can take advantage of this functionality when invoking package execution using either the DTExec command line utility with the appropriate switches or its graphical interface equivalent DTExecUI (both executables reside, by default, in the Program Files\Microsoft SQL Server\100\DTS\Binn folder). DTExec provides the ability to execute packages directly from the Command Prompt. Assuming that you saved our sample package as a .dtsx file, you need to specify its name (and, potentially, location) following the /FILE switch (/SQL is appropriate when dealing with packages stored in SQL Server databases). The /SET switch is intended for assigning values to properties of package components and variables (identifiable by their package paths). For example, in order to list all Redmond-resident employees listed in the vEmployee view of AdventureWorks database on our default SQL server, we would execute the following.

DTExec /FILE Package.dtsx /SET "\Package\Data Flow Task.Variables[User::City].Properties[Value]";"Redmond"

Note that this assumes that Package.dtsx resides in the same directory from which we launched DTExec utility. Similarly, if we wanted to extract equivalent data but target a different SQL Server (called in our example simply OTHERSQLSERVER, we could accomplish this by running the following.

DTExec /FILE Package.dtsx /SET \Package.Variables[User::srcServer].Properties[Value];OTHERSQLSERVER

While following this approach to assign a value to an individual property or variable is relatively convenient, using it for even a few of them becomes challenging due to the sheer length of the resulting command line. Fortunately, this is easily resolvable by taking advantage of the /COM switch, which allows you to point to a file containing additional settings. Similarly, you have an option to designate an arbitrary configuration file that is supposed to be applied prior to package execution (via /CONF switch). However, you should keep in mind that any settings assigned via existing configurations (defined at the design time and included in the package via Package Configuration Organizer) will always take precedence over those used in combination with /SET, /COM, or /CONF switches of DTExec (DTExecUI is a subject to the same rule). This is because configurations included in the package are applied twice, before and after settings specified via either of these two utilities are taken into account. Such design, while somewhat inflexible, gives you the ability to assign desired values directly at the invocation time, as long as they do not conflict with those already present in existing configurations.

Finally, let's briefly review the interface of DTExecUI, also known as Execute Package Utility. In short, it is simply a graphical, more friendly equivalent of a bit more cumbersome DTExec, where each of the switches we just discussed can be set through standard GUI elements (accessible via Set Values, Command Files, and Configurations sections in its straightforward to navigate window). However, the main drawback of DTExecUI is its inferior performance (when compared with DTExec) primarily due to the overhead of its Logging functionality. In addition, despite apparent simplicity, you are still forced to type in an appropriate package path for target properties or variables in the Set Values section (rather than being able to browse for them in the way this feature is implemented in Select Properties to Export page of Package Configuration Wizard of Business Intelligence Development Studio).

» See All Articles by Columnist Marcin Policht