RetainSameConnection - use or not?

Question

Would there be any use cases where RetainSameConnection should be false?

I don't quite see why it's false by default and am wondering if maybe the setting does have a right to exist.

Answer 1

If you're not repeatedly hitting a datasource during package execution, leave RetainSameConnection as false so the connection can be destroyed and returned to the pool at the end of use.

As you're repeatedly hitting the same datasource, set RetainSameConnection to true. You'd also want this when setting a session variable (such as MySQL's SQL_BIG_SELECTS) or creating a temporary table for use within the package.

Answer 2

Two reasons: 1. Connections are expensive 2. Parallelism is effective First, one myth to get out of the way: RetainSameConnection false does NOT cause the connection to get closed after every task. (Nor does true.)

Under default configuration, SSIS will open as many connections to the database as it deems necessary to issue commands. For example, you may have two tasks unconstrained by precedence operators, operating on two different tables on the same connection. SSIS may decide to open two instances of the connection in order to process the two commands independently. Another scenario is that you may have a single data flow with a Source component and Destination component targeting the same connection. SSIS may decide to use two connection instances, one for the Source, and another for the Destination. Again - the intent of SSIS is to use separate connections to improve data throughput.

So - RetainSameConnection set to false is almost always a GOOD THING. There is usually a pretty good reason why defaults are defaults.

There are very few scenarios for RetainSameConnection to be true: 1. You wish to use database-controlled transactions. If SSIS chooses to open several connections to accomplish the task(s), your SQL transaction won't cover them all. Forcing SSIS to only open one ensures your commands are all enlisted in the same transaction. 2. Your database is "underpowered" or under load, and you wish to reduce the impact of your SSIS package. Restricting SSIS to only one connection to it can help with that.