I explain what the RetainSameConnection property on an Integration Services connection does:
My co-worker had set up a dynamic connection (see Rafael Salas or Hari Bagra for details on how to do this), but something weird was happening: the package was trying to push everything to the same server. I confirmed that if all relevant customers loaded were for the same server, that the process would work correctly, and that I could run each server load one at a time, so there weren’t any problems connecting to particular servers or parameters overriding this choice. It’s like the connection was “sticky,” connecting successfully to the first server and then ignoring the later changes.
RetainSameConnection allows for certain benefits, but has specific limitations. Click through to see those details.