Datasheet

Chapter 1: SSIS Solution Architecture

For the Execute SQL Task and Bulk Insert Task, the SQL code or BCP command is executed on the

machine that the connection specifies. This is different from the Data Flow Task, which runs on the

machine where the package is executed.

Package Execution and the Data Flow

For your packages that have data flows (which is probably most of your packages), you should

understand what happens to the data based on where you execute that package (with the embedded

data flow). Additionally, understanding where the data flow execution impact will be dictates where you

decide to run your packages.

The data flow impact on the package execution server involves the resources needed to manage the data

buffers, the data conversion requirements as data is imported from sources, the memory involved in the

lookup cache, the temporary memory and processor utilization required for the Sort and Aggregate

transformations, and so on. Essentially, any transformation logic contained in the data flows is handled

on the server where the package is executed.

The following examples are common configurations for where data is sourced, the destination location,

and where packages are executed. Obviously, data flows can be varied and complex with multiple

sources and destinations, so this simplification provides the framework with single - source locations and

single - destination locations.

Packages Executed on the Source or Destination Servers

The most common example is when a package (that contains a data flow) is executed on either the

source or destination server, assuming they are separate.

Figure 1 - 10 shows the data path and impact on the environment when the package is executed on the

machine where the source data is located.

Figure 1-10

Destination files/data

Destination Data Path

Execution Impact

Extraction Impact

Load Impact

Execution Location

Source files/data

c01.indd 25c01.indd 25 9/24/09 11:26:40 AM9/24/09 11:26:40 AM