SQL Server 2005 Integration Services - Package Execution - Part 24
May 12, 2006
Continuing our coverage of topics dealing with various aspects of managing SQL Server 2005 Integration Services packages, which we started with an overview of their storage and deployment, we now turn our focus to their execution. While SQL Server 2005 offers a number of different ways to launch packages, the one, which, by far, has been our primary choice throughout this series, involves menu options, toolbar buttons, and keyboard shortcuts available in SSIS Designer of the Business Intelligence Development Studio:
The main benefit of the first three options listed above is the ability to track run-time characteristics of a package and to identify potential errors during development stage, directly from the SSIS Designer interface. (Note that, as presented in our previous articles of this series, it is also possible to execute individual Control Flow tasks using their context sensitive menu, as well as add breakpoints and logging to enhance troubleshooting capabilities). On the other hand, executing a package in such a manner introduces significant overhead (due to debugging functionality as well as features incorporated into the SSIS Designer, which affect utilization of system resources) and does not represent adequately its performance characteristics in a production environment. This means that packages executed in such manner perform typically worse than when launched either outside of the Business Intelligence Development Studio or using the last option listed above (Start Without Debugging menu item or Ctrl+F5 key combination), roughly equivalent to running a package with DTExec.exe utility (as long as all the application windows are minimized), which we will describe shortly.
Another, already familiar method of executing a package is incorporated into SQL Server Import and Export Wizard. Note, however, that availability of this feature depends on how the wizard is started. While you can create and save a package (and launch it afterwards) with the "SSIS Import and Export Wizard..." entry in the Project top level menu of Business Intelligence Development Studio, immediate execution is possible only when you initiate the wizard from the Command Prompt (by invoking the DTSWizard.exe executable located in the Program Files\Microsoft SQL Server\90\DTS\Binn folder) or from the SQL Server Management Studio (by selecting Import Data... or Export Data... items under Tasks entry in the context sensitive menu of any non-system database listed in the Object Explorer).
If you like the flexibility provided by the Command Prompt-based environment
(which yields itself easily to automation via scripting or batch files), you
might want to consider using the DTExec utility (located in Program
Files\Microsoft SQL Server\90\DTS\Binn folder), which replaces its SQL Server
2000 Data Transformation Services equivalent, DTSRun.exe. We covered some of
its features in one of
our earlier articles, when we discussed its ability to reference package
configurations and dynamically assign values to properties and variables. (More
specifically, the latter is accomplished with the /Set switch - although
connections, logging, and progress indicators are configurable as well with
/Connection, /ConsoleLog, /Logger, and /Reporting switches, respectively).
Packages can be loaded from Microsoft SQL Server msdb database, SSIS Package
Store, or file system. For more details regarding DTExec.exe, review its
options by typing
If you find the Command Prompt nature of the DTExec.exe too confusing, you can resort to Execute Package Utility, which provides the same functionality via a much friendlier graphical interface (and is the replacement for DTSRunUI.exe from SQL Server 2000 Data Transformation Services). To invoke it, simply type DTExecUI.exe at the Command Prompt or the Start->Run window (in the Open text box). The window is divided into multiple sections, a listing of which is displayed on the left hand side, consisting of:
Note that the sections we listed (except for the last one) correspond to parameters of the DTExec.exe utility (as a matter of fact, DTExecUI invokes DTExec.exe once you click on its Execute command button, passing parameters to it, based on the selections you made).
As we explained in our previous article, packages can be saved to SSIS Package Store or msdb database from the Object Explorer window in SQL Server Management Studio. From within the same interface, it is also possible to monitor the execution of packages stored in this fashion. Keep in mind, however, that this ability depends on having the SSIS Service operational (which should be the case under normal circumstances, since, by default, its startup type is set to Automatic). The SSIS service, which was introduced in SQL Server 2005, provides additional management support for storage and execution of packages (this does not imply in any way that packages can not be executed if the service is not running). SSIS Service also benefits the startup time of packages invoked from Business Intelligence Development Studio, by caching reference information about their components.
Selecting the "Run Package" entry from the context sensitive menu of a package node saved into SSIS Package Store or SQL Server (represented, respectively, by the File System and MSDB nodes in the Object Explorer window of SQL Server Management Studio) will automatically activate the Execute Package Utility window (DTExecUI.exe), from where you can select the appropriate set of parameters. To launch the package, click on the Execute command button, which, in turn, will display runtime details in the Package Execution Progress window (the same window also gives you a chance to interrupt execution by clicking on the Stop command button). Packages launched in this manner can be monitored via the Running Packages node in the Object Explorer window.
Regardless of a package location, execution initiated via DTExecUI and DTExec always takes place on the system from which they are launched. In addition, there is no mechanism native to SSIS that would allow scheduling such execution. However, both of these features can be implemented with the help of SQL Server Agent-based functionality. More specifically, it is possible to define and schedule a job, consisting of one or more steps of "SQL Server Integration Services Package" type (which contains all relevant SSIS package configuration settings identical to the ones we described earlier when discussing DTExecUI.exe interface) or of "Operating System (CmdExec)" type (for which you simply need to supply the command line containing DTExec.exe and appropriate listing of its parameters). The benefit of the first option is ease of configuration, however the second one is useful when troubleshooting, since it provides more detailed error messages. Note that in both cases, steps are executed by default in the security context of the SQL Server Agent Account. This might cause a problem if you rely on the default protection level of "Encrypt sensitive data with user key" (since it is likely that the account used to save the package is different from the one that is being used to launch it). To resolve this issue, consider applying "Do not save sensitive data" protection level and supply the relevant information during execution via package configurations.
You also have an option to execute one package as part of another (in a child-parent relationship) by employing the Execute Package Control Flow task in the SSIS Designer of the Business Intelligence Development Studio. This encourages package reusability and modular design of more complex data processing workflows. It also enhances security, by accommodating scenarios where development work is split across several independent teams. Packages executed in such manner can reside in either file system or msdb database. Their location is specified in the Package section of the Execute Package Task Editor window, using the Location and Connection entries (defined with OLE DB connection manager or File connection manager, depending on the package store type). Password entry in the same section of the editor window is useful in situations where a child package is (or needs to be) password protected. The ExecuteOutOfProcess entry controls whether the child package executes in its own process, separate from the parent's (with the default value set to False, both are sharing the same process space). Changing its value to True increases the amount of processing resources, but makes their execution independent, allowing one to complete even if the other one fails.