Datasheet

Chapter 1: SSIS Solution Architecture
27
Following are some of the benefits of executing a package on a destination server:
Limited impact on your source server if it is running other critical tasks
Potential performance benefits for data inserts, especially because the SQL Destination
component can now be used
Licensing consolidation if your destination server is also running SQL Server 2008
One drawback of this approach is that it has a heavy impact on the destination server, which may affect
users interacting with the destination server.
This approach is very useful if you have users querying and using the destination during the day, and
your SSIS processing requirements can be handled through nightly processes.
Standalone SSIS Servers
An alternative execution option is to run your SSIS packages on a second or third server.
In Figure 1 - 12, an SSIS package is executed on a second server, and, in this diagram, both the source and
destination are on the same machine.
❑
❑
❑
Figure 1-12
Source Data Path
Network Impact
Destination Data Path
Execution Impact
Load Impact
Destination files/data
Execution Location
Source files/data
Execution Impact
As you can see, this scenario is not ideal, because the data would need to travel over the network to the
second server, be processed, and then travel back to the same original machine, creating potentially high
network utilization, depending on the volume. However, it would reduce the resource impact on the
data source and destination server.
Using a standalone SSIS server if your sources and destinations are not on the same physical machines
makes more sense. Figure 1 - 13 highlights this architecture.
c01.indd 27c01.indd 27 9/24/09 11:26:42 AM9/24/09 11:26:42 AM