We have an AS400 source system and can only use ODBC connections to access it. We also need to set RetainSameConnection to true since we're initializing the AS400 connection with a number of temporary aliases that will be used by later Data Flows and Execute SQL Tasks.
Turns out that when using the ODBC driver in SSIS with Execute SQL & Data Flow Tasks, there is a bug that makes it forget the RetainSameConnection setting (it will always revert back to false when saved & reopened.) When the connection is closed and new one created, our carefully created aliases are of course gone (since they were connection specific.)
Some take aways:
* Beware of this bug, the effect of not having RetainSameConnection is often subtle and hard to spot
* RetainSameConnection _does_ work with OLEDB (although OLEDB is not really an option with out AS400)
* The bug is fixed in SP2. There is a CTP of SP2 coming very soon to a web very near you;-)
So let's be careful out there!
Kristian