SQL Server 2012 Integration Services introduced a new approach to manage the deployment of packages developed using SQL Server Data Tools, referred to as Project Deployment Model, while still maintaining support for traditional methodology (labeled as Package Deployment Model). As the result, 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 either in 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).
In our most recent article published on this forum, we have described one way to handle package execution in the last of these three scenarios (which involved the use of SQL Server Management Studio and leveraged the new SSIS feature known as environments to assign values of package and project parameters). Now it is time to explore another method of running SSIS packages that relies on the dtexec.exe utility.
Dtexec.exe offers the most universal (and, at the same time, also the most versatile) capabilities in regard to executing packages, regardless of their location and storage type. It can be used not only to trigger package execution but also to control runtime behavior by modifying such package characteristics as properties, variables, environments, connection managers, parameters, or logging settings. It is available in 64- and 32-bit versions (the latter is included in Client Tools and SQL Server Data Tools). It is important to note that on systems with the 64 bit SQL Server 2012 Integration Services, the default behavior favors the 32-bit code (if both versions have been installed), since the location Program Files(x86)\Microsoft SQL Server\110\DTS\Binn appears before Program Files\Microsoft SQL Server\110\DTS\Binn in the PATH environment variable. This can be remediated by reversing the sequence of these entries in the PATH environment variable. (You can alter its value via the System applet in the Control Panel or by directly editing Windows Registry, but keep in mind the standard disclaimer about registry modifications), by calling dtexec.exe with its fully qualified file system path, or by making Program Files\Microsoft SQL Server\110\DTS\Binn your current directory (within the Command Prompt).
The syntax of dtexec.exe takes the form dtexec /option [value] [/option [value]] ..., pairing option names with values associated with them. For example, /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). Identifying package location is fairly straightforward; however obtaining the same information about individual package components (such as properties, variables, connections, parameters) tends to be a bit more challenging since it requires familiarity with SSIS property path notation.
Let's examine this concept in the context of packages stored in the .dtsx format in the file system, which implies that they are part of the legacy Package Deployment Model (we will deal with the implications of Project Deployment Model on dtexec.exe operations in an upcoming article), starting with the property paths of the variables (refer to one of our earlier articles for more information on the subject of SSIS variables). In order to reference a variable via the property path notation, you need to provide its scope, namespace, and name. As a general rule (in the case of the Package Deployment Model), 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 its name (in the case of Properties) or (when dealing with Variables, where duplicates are allowed) also their namespace, with the double colon (::) as the separator.
In order to become more accustomed to this syntax, let's consider a sample package that contains a single Data Flow Task with its default name and two user variables. Var1 has the package-wide scope while the visibility of Var2 is limited to the Data Flow Task only. With these settings in place, you can reference our variables in the following manner:
\Package.Variables[User::Var1].Properties[Value] \Package\Data Flow Task.Variables[User::Var2].Properties[Value]
It is also possible to use property paths to reference properties of package components directly (rather than through variables). Let's review how this can be accomplished with a sample package that contains two connection managers (one providing access to a text file and the other to the AdventureWorksDW database hosted by the local instance of SQL Server), each with its own ConnectionString property. The latter also includes UserName and Password properties that you might need to specify if not relying on Windows authentication. If you wanted to reference these properties directly, you could specify:
\Package.Connections[Flat File Connection Manager].Properties[ConnectionString] \Package.Connections[localhost.AdventureWorksDW].Properties[ConnectionString] \Package.Connections[localhost.AdventureWorksDW].Properties[UserName] \Package.Connections[localhost.AdventureWorksDW].Properties[Password]
Now let's see how we can take advantage of the property paths when executing our sample package (whose creation was described in an earlier article published on this forum) stored in the file system (as the Package.dtsx file) outside of locations controlled by the SSIS service. In the most basic scenario, we could launch Package.dtsx residing in the current directory by running the following from the Command Prompt (we are leveraging the predefined environment variable %ProgramFiles% here, which points to the Program Files folder on the local disk):
%ProgramFiles%\Microsoft SQL Server\110\DTS\Binn\dtexec.exe /F Package.dtx
If our intention was to redirect the output to an alternate file system location (other than the one defined in the package), we could assign its path directly to the ConnectionString property of Flat File Connection Manager using the following syntax (D:\FlatFileExport.txt serves here as an example of intended destination):
%ProgramFiles%\Microsoft SQL Server\110\DTS\Binn\dtexec.exe /F Package.dtx /SET "\Package.Connections[Flat File Connection Manager].Properties[ConnectionString]";"D:\FlatFileExport.txt"
While this approach to assigning arbitrary values to individual properties or variables is relatively convenient, using it for a larger number of changes becomes cumbersome. Fortunately, this can be easily remediated by taking advantage of the /COM switch, which allows you to point to a file containing multiple /SET options (which subsequently are applied once the package is launched). 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. This results from the fact that configurations included in the package are applied twice, before and after settings specified via dtexec.exe are taken into account.
In our next article, we will explore other capabilities of dtexec.exe that allow you to run packages residing in SSISDB catalog or controlled by SSIS service. We will also explore features offered by its graphical counterpart dtexecui.exe.