Datasheet

Chapter 1: SSIS Solution Architecture
24
Package Execution Location
When you are planning out your SSIS solution architecture, you must consider your package execution
strategy. Your objective is to leverage the servers and network bandwidth that can handle the impact
load from package execution, but without impacting resources that need primary performance. When it
comes to where a package should be executed, there is no absolute answer. However, some general
principles can direct one architecture design over another.
Package Storage Location Versus Execution Location
When it comes to running a package, a difference exists between where a package is run and where that
package is stored. You can store a package as a file and put it in a file system folder, or you can load a
package into the
msdb system database in SQL Server 2008. Either way, when the package is executed,
the storage location is merely where the metadata of that package lives. The package is loaded from that
source location through an execution method, and run on the machine where the execution is kicked off.
In other words, if you are running a package through the command line or through a job, the package
will run on the machine where the command line is called, or the job runs.
Figure 1 - 9 shows the storage location server on the left and the execution server on the right. The
package is executed on the server on the right, even though the package is stored on the server on
the left.
Figure 1-9
Execution Location
Package Storage Location
Package Load Path
Resource
Impact
Package Execution
BIDS
DTExecUI
DTSExec (cmd/bat)
SQL Agent
Programmatic
File System Storage
MSDB Storage
or
Execute SQL Task and Bulk Insert Task Execution
Although a package may be run on any machine with the SSIS service (or, really, that has the SSIS
executables and DLLs), this isn ’ t necessarily the place where all the work is being done in the package.
For example, if you run an Execute SQL Task in a package and execute the package on a different server
than where the Execute SQL Task connection is defined, then the SQL statement is run where the
connection is configured, not on the SSIS execution machine. To be sure, the workflow coordination will
still be handled on your SSIS execution machine, but the actual SQL code would be run on a different
machine.
c01.indd 24c01.indd 24 9/24/09 11:26:39 AM9/24/09 11:26:39 AM