SQL Server 2012 Integration Services - GUI-Friendly Ways of Managing Execution of SSIS Packages

Thursday Sep 20th 2012 by Marcin Policht

Marcin Policht has been discussing the use of dtexec.exe command line utility in executing SSIS projects and packages in recent articles. This overview summarizes your choices for GUI-based methods of running SSIS packages.

In our articles published recently on this forum, we have been discussing the use of dtexec.exe command line utility in executing SSIS projects and packages. Its superior role in this area results primarily from its flexibility, which not only allows you to control execution behavior in a very granular manner, but also provides access to any packages and projects, regardless of their format or location. (Obviously this capability is a subject to standard file system and database protection mechanisms).

As a reminder, let's briefly review three different SSIS package storage types available in SQL Server 2012. The number of available options increased compared with its predecessors, due to the introduction of a new method of managing deployment of packages developed using SQL Server Data Tools (referred to as Project Deployment Model), while still maintaining support for traditional approaches (labeled as Package Deployment Model). Effectively, SSIS packages can be stored in one of three different ways:

  • 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 folder within the Integration Services node of 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)

Let's start our overview by exploring characteristics of the DtExecUI.exe utility, which is a 32-bit (and hence, executing using Windows on Win32 - also known as WOW - mechanism on 64-bit system), GUI-based counterpart of the 32-bit version of dtexec.exe (the former resides in Program Files(x86)\Microsoft SQL Server\110\Tools\Binn\Management Studio folder, while the latter can be found in Program Files(x86)\Microsoft SQL Server\110\DTS\Binn). Despite this apparent equivalence, the graphical utility does not offer feature parity, since it is not capable of executing packages residing in Integration Services Catalog (which happens to be the latest and most recommended storage type). In addition, it is not available in the 64-bit format (unlike dtexec.exe, which 64-bit version is located in Program Files\Microsoft SQL Server\110\DTS\Binn folder).

However, for those who are command-line challenged, DtExecUI.exe constitutes a welcomed addition to the arsenal of SSIS tools, which might also help with discovering capabilities of dtexec.exe. Once you launch it, you will be presented with the Execute Package Utility window, where you need to select the package source type (SQL Server, File System, or SSIS Package Store), the name of the target server, the authentication mechanism (Windows or SQL Server), as well as the package location and name. For each of the package types, you can modify execution behavior by:

  • specifying files containing configurations and command lines
  • modifying connection strings of connection managers
  • assigning execution options (such as, Validate package without executing or Enable package checkpoints)
  • controlling level of details reported during runtime
  • adding logging providers
  • setting runtime values of properties of package objects
  • stipulating conditions that need to be satisfied in order for the invocation to take effect (such as the presence of package signature or a match on package build number, ID, and version ID).

All of the options you choose translate into a sequence of parameters displayed on the Command Line section of the Execute Package Utility dialog box, which you can optionally edit, if desired, before launching the package by clicking on the Execute button. The outcome is displayed in the Package Execution Progress window. It is important to note that output capture is one of the factors affecting speed of DTExecUI. As a matter of fact, due to its limited functionality (lack of support for Project Deployment Model, including environments as well as 32-bit only implementation) and inferior performance characteristics, DtExecUI.exe is intended to be used primarily for backward compatibility.

In the earlier versions of SQL Server, you could execute packages stored in locations controlled by the SSIS service using SQL Server Management Studio after establishing a connection to the Integration Services component. This method still remains valid and, just as was the case previously, all available packages appear, depending on their placement, in either File System or MSDB subfolder of Stored Packages folder within the Object Explorer window. To launch a package, locate it in the folder hierarchy and select the Run Package entry from its context sensitive menu. If you explored the first of the execution methods discussed here, you will notice that this option triggers the display of the Execute Package Utility window, which constitutes the graphical representation of DtExecUI.exe. As a matter of fact, these two methods are equivalent - so, effectively, this one is a subject to the same set of limitations we described in the previous paragraph. In addition to the Package Execution Progress window, SQL Server Management Studio also offers the ability to view currently executing packages by accessing the Running Packages folder within the Integration Services node.

Starting with SQL Server 2012, you also have an option to execute packages contained in projects deployed to SSISDB catalog by leveraging functionality included in SQL Server Management Studio. In this case, however, you need to connect to Database Engine hosting SSISDB Catalog (rather than Integration Services). Once the connection is established, navigate to the Integration Services Catalog folder hierarchy and locate a parent folder representing the project containing the package you intend to execute. The folder structure includes two subfolders, named, respectively Projects and Environments (you can find more information about the latter by referring to one of our earlier articles). In the first of them, you will find the Packages subfolder containing all of its packages. Selecting Execute... entry from the context sensitive menu of the one you want to launch will display the Execute Package window, from where you can adjust Parameters and Connection Managers, as well as specify Advanced settings (such as Property overrides, Logging level, Dump on errors, and 32-bit runtime). You can also choose an environment in which execution should take place (obviously assuming that you have defined them first). To track package execution and validation, select the Reports entry from its context sensitive menu, which allows you to generate both standard and custom reports, including performance related statistics.

Finally, it is also possible to launch package execution directly from the SQL Server Data Tools interface, although in this case, you are limited to packages stored in the file system only (in particular, those which are currently opened in the Solution Explorer window). In general, under these circumstances, you have two options.

  • The first one, geared specifically towards development objectives, provides the ability to debug your code and can be invoked by pressing the F5 function key, clicking the green arrow button in the Standard toolbar, or selecting Start Debugging item from the Debug menu. The resulting actions are reflected by changes in the graphical interface of the Designer window, providing visual, color-coded clues representing the current execution status. It is also possible to derive this information by viewing the Execution Progress tab (providing that Debug Progress Reporting option in the SSIS menu is enabled) which offers more insight into each stage of the runtime. After you stop the execution, the equivalent content is displayed in the Execution Results tab.

One of the primary benefits of this option is the ability to track package execution in a very detailed manner, by taking advantage of various debugging features incorporated into SQL Server Data Tools (such as breakpoints or a variety of debug windows). Unfortunately, each of these features introduces processing overhead, affecting system performance and resulting typically in considerably longer execution times. This is where the second option comes into play.

  • SQL Server Data Tools include the ability to execute packages in the manner that eliminates impact associated with debugging functionality, which allows you to properly evaluate their performance characteristics. This is accomplished by pressing Ctrl+F5 keys or selecting the Start Without Debugging item from the Debug menu. In this case, the execution is carried out by directly invoking dtexec.exe, with the progress displayed in the automatically launched Command Prompt window.

This overview summarizes your choices for GUI-based methods of running SSIS packages. There are, however, a few additional ways of dealing with their execution in the unattended manner, which we will explore in upcoming articles published on this forum.

See all articles by Marcin Policht

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved