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:
- "Start Debugging" button in the Standard toolbar,
-
"Execute Package" entry in the context-sensitive menu
of a package node in the Solution Explorer window, -
"Start Debugging" entry in the Debug top level menu (or
F5 function key), -
"Start Without Debugging" entry in the Debug top-level
menu (or Ctrl+F5 key combination).
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 FilesMicrosoft SQL Server90DTSBinn 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
FilesMicrosoft SQL Server90DTSBinn 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 DTExec.exe /?
at the Command Prompt or check the "dtexec Utility" entry in the SQL
Server Books Online.
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:
-
General (where you specify package source type – such as SQL
Server, File system, or SSIS Package Store, the corresponding location, as well
as authentication type) -
Configurations (allowing you to point to package configuration
files that should be used at runtime) -
Command Files (providing the ability to include files that
contain additional commands to be executed, launched in the order in which they
are listed) -
Connection Managers (facilitating modifications to connection
strings for selected Connection Managers) -
Execution Options (defining runtime properties related to
validation and restarts, in cases where checkpoints are used) -
Reporting (determining how detailed the level of reporting should
be) -
Logging (setting log providers and configuration string for
each) - Set Values (where you assign values to package properties)
-
Verification (restricting execution to a specific package, based
on such criteria as presence of digital signature, package build, ID, or
version number) -
Command Line. The Command Line section offers the ability to
review and edit the syntax of the resulting DTExec.exe command (reflecting all of
the options selected via the graphical interface), which you can subsequently
include in your batch files or simply execute from the Command Prompt.
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.
