I wonder if anyone had experienced anything similar to the following problem?
I have a SSIS package containing 10+ data flow tasks which I'm using to extract data from an OpenEdge 11.3.002/Progress database to SQL Server 2016 via an ODBC driver.
However, I've been informed by the dba of the progress database that I've created so many connections to the Progress db via SSIS that on several occasions they've needed to restart the Progress database to kill all the connections that SSIS has made as no-one else can connect to it anymore.
I've never experienced an issue when SSIS makes 'too many connections' to a database so I'm trying to reduce the number of connections it opens to Progress. Approaches I've tried so far:
- On the connection manager to the Progress DB in SSIS I've set the
Retainsameconnectionproperty to true;
Maxconcurrentexecutablesproperty is set to 2 (So only two data flow tasks can run concurrently);
Enginethreadsproperty is set to 5.
But these don't appear to limit the number of connections (enough).
Are there any other ways I can reduce the number of connections SSIS makes to another ODBC database to say max 100 connections (And ensure SSIS releases these afterwards)?
- Progress Openedge db 11.3 to extract data from
- Visual Studio 2015 for SSIS packages
- SQL Server 2016 to load data into