In our most recent article published on this forum, we described the way to leverage the dtexec.exe utility to execute SQL Server 2012 Integration Services (SSIS) packages stored in the traditional .dtsx file system format. However, as you might recall from our earlier discussions on the subject, of two SSIS deployment models (Package and Project), this is just one of several possible storage types available in the current version of the product, all of which dtexec.exe is perfectly capable of handling. In addition, this versatile utility offers a number of auxiliary features helpful in automating a variety of package management tasks. We will focus here on exploring this less commonly known functionality and present examples illustrating its use.
As a reminder, SSIS packages can reside in any of the following locations:
- file system outside of locations controlled by the SSIS service.
- file system and SQL Server locations controlled by the SSIS service (residing in either SSIS Package Store or MSDB database and appearing in the Stored Packages subfolder of the Integration Services node within the SQL Server Management Studio console).
- contained in projects deployed to SSISDB catalog (appearing in the Integration Services Catalog subfolder of the SQL Server Database Engine node within the SQL Server Management Studio console).
Execution of packages deployed to file system (applicable to the first two of these options) can be handled by employing techniques described in the aforementioned article. As we pointed out there, the syntax of dtexec.exe takes the form of dtexec /option [value] [/option [value]] ..., which pairs option names and the values associated with them. In particular, each of the /SQL, /FILE, /DTS, or /ISServer options designating the package storage type (SSIS Package Store, File System, MSDB database, and SSISDB database, respectively) can be combined with a value that specifies package location (as a relative or absolute path). Let's see how we can apply this principle in practice and use dtexec.exe to execute packages and projects deployed to different SQL Server-based locations.
In order to view packages controlled by the SSIS service, launch SQL Server 2012 Management Studio and connect to its Integration Services component. Once the connection is established you should be able to locate Stored Packages folder in the Object Explorer window, with File System and MSDB subfolders. The latter of the two contains a customizable folder hierarchy, with packages (deployed using Package Deployment model) as its leaf-level subnodes. For the sake of example, let's assume that our sample package, named Package1, resides in the Folder1\Sub1 subfolder (within MSDB content) and that we can execute it with our current Windows credentials. In these circumstances, you could invoke the package execution by running the following from the Command Prompt:
dtexec /SQL Folder1\Sub1\Package1
By default, the dtexec utility displays output containing individual stages of execution, including the timestamp and percentage of work completed for each, along with the final runtime statistics, such as returned value (indicating success or failure), or started, finished, and elapsed times. You have the option of increasing the level of details by specifying /ConsoleLog (or simply /Cons) switch, which by default is quite extensive, providing functionality equivalent to the traditional SSIS runtime logging. (For more information about the types of entries you might find there, refer to our earlier article on this subject). It is possible to reduce its scope to arbitrarily designated data fields only by specifying their associated labels:
- N for name of event that triggered the log entry
- C for computer where the event took place
- O for operator providing security context for the event
- S for the name of the SSIS entity where the event originated
- G for the GUID of that entity
- X for the GUID of the execution instance
- M for the actual log message generated
- T for the start and end times
In addition, you can include or exclude specific entries by appending I or E letter followed by a specific event name, source name, or GUID (separated by the semicolon). For example, if your intention was to limit the output to log entries generated by the SSIS component named Data Flow Task included in our sample package, you would use the following syntax:
dtexec /SQL Folder1\Sub1\Package1 /ConsoleLog I;"Data Flow Task"
Now let's turn our attention to the execution of packages stored in the Integration Services Catalog. SQL Server 2012 Integration Services introduces an innovative approach to deploying SSIS projects, known as Project Deployment Model. This is the default and recommended deployment method, due to a number of benefits it delivers (such as, the ability to centralize managing of package properties across deployed projects, as well as monitoring and logging of package execution performance and progress). Fortunately dtexec.exe utility has been updated in order to facilitate this new deployment methodology.
To view projects and packages deployed in this manner, launch SQL Server 2012 Management Studio, connect to the target Database Engine instance, and navigate to the Integration Services Catalogs folder appearing in the Object Explorer window. You will find there the default catalog labeled SSISDB with its content organized in a semi-customizable hierarchy. Names of its top level folders are assigned arbitrarily during project deployment. Each of them contains a subfolder named Projects, hosting deployed projects, which in turn stores individual packages within the subfolder named Packages. Effectively, if you deploy the project, Project1, with a single package Package1.dtsx and decide to save them within the Folder1 folder, the full path within Integration Services Catalog becomes \SSISDB\Folder1\Projects\Project1\Packages\Package1.dtsx. However, when executing such packages with the ISServer switch (intended for packages deployed to the Integration Services Catalog), you need to exclude the predefined containers (i.e. Packages and Projects), which yields the following syntax; (keep in mind that this method supports only Windows integrated authentication).
dtexec /ISServer "\SSISDB\Folder1\Project1\Package1.dtsx"
The /Server switch allows you to specify name of the target SQL Server instance to which the project and its packages have been deployed (this is required when invoking the execution from a remote computer or using a non-default instance). The /Parameter switch can be used to assign values to parameters of projects and packages, server-level options (identifiable by the $Project::, $Package::, and $ServerOption:: prefixes, respectively), as well as connection managers (denoted by the CM. prefix). In each of these cases, an actual value is appended at the end of the string, separated by the semicolon. For example, in order to set the project-level parameter named Parameter1 to the value Value1 in our sample package, you would execute the following (obviously, if needed, the /Parameter switch can be used multiple times within the same command line to assign values to any additional parameters):
dtexec /ISServer "\SSISDB\Folder1\Project1\Package1.dtsx" /Parameter $Project::Parameter1;Value1
You also have the ability to take advantage of environments, introduced in SQL Server 2012 Integration Services, which are intended to serve as the replacement for package configurations in the context of the Project Deployment Model. In the most basic terms, they can be described as collections of arbitrarily defined variables mapped to parameters of packages and projects stored in SSISDB catalog. When executing packages via dtexec, you can reference arbitrary environments by applying the /EnvReference (or simply /Env) switch, followed by the environment identifier. (You can determine their values by reviewing content of the Environments subfolders within the SSISDB folder hierarchy of Object Explorer in SQL Server 2012 Management Studio).
The default output displayed in the Command Prompt window following execution of SSISDB resident packages includes only the started, finished, and elapsed times. Details of the execution can be examined by leveraging centralized logging implemented as part of the Integration Services Catalogs. More specifically, the context-sensitive menu of the package node within SSISDB folder hierarchy includes Reports entry, with Standard Reports and Custom Reports cascading menu options. The first of them offers a convenient way to obtain data about package invocations, including their overview, all of the auto-generated messages, as well as performance statistics.
Dtexec.exe also allows you to test execution of a package that is part of a project that has not been deployed yet (and, if needed, assign arbitrary package or project-level settings). This is accomplished by taking advantage of the /Project switch, which points to the location of the .ispac file. When employing this option, you need to specify the package name (in combination with the /Package switch). /Set switch (which we described in more detail in our previous article) allows you to assign values to both package and project level parameters (incidentally, /Set can also be used with /ISServer switch to configure property overrides). For example, in order to execute Package1.dtsx package included in our sample project stored in the D:\Data\SSIS\Projects\Project1\Project1.ispac file and assign Value1 to the project-level parameter named Parameter1, you would run the following:
dtexec /Project "D:\Data\SSIS\Projects\Project1\Project1.ispac" /Package Package1.dtsx /Set \Package.Variables[$Project::Parameter1];Value1
There are several other features of dtexec.exe (such as, options facilitating validation, verification, or use of checkpoints) that warrant further coverage, which we intend to discuss in our future articles. However, the information presented so far should give you a better understanding of the versatility and power of this utility and encourage you to take advantage of its capabilities.