Expert SQL Server™ 2005 Integration Services

12.3. Package Execution Principles

Scalability is also a matter of designing a package execution strategy that minimizes resource impact (memory, processor, and I/O capabilities) on the servers that need resources for other purposes or have little resources, and leveraging the servers that have resource availability or that can handle the impact load from package execution. When it comes to where a package should be executed, there is no absolute answer. However, there are general principles that can direct one architecture design over another.

12.3.1. Package Storage Location versus Execution Location

First of all, when it comes to running a package, there is a difference between where a package is run and where that package is stored. A package can be stored as a file and put in a file system folder, or a package can be loaded into the MSDB system database in SQL Server 2005. 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 DTExec, the package will run on the machine where DTExec is called, not the place where the SSIS package is stored.

Figure 12-16 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, where the resources are ...