I'm debugging a SSIS package running on SQL Server 2008 R2. It reads from a staging table and writes to one of two tables. New rows get written directly to a destination table; modified rows get written to an update table that is used later on to update the destination table.
While monitoring this package in action on a large staging table, I noticed that there are two active connections:
- to the staging table, which lives in a staging database on the same instance as the destination table.
- to the destination table, which is on the same instance as the staging databse but in a different database
I was surprised to see that the connections are using TCP instead of Shared Memory. Any clues as to why this might be and how to change it?