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:

  1. On the connection manager to the Progress DB in SSIS I've set the Retainsameconnection property to true;
  2. Maxconcurrentexecutables property is set to 2 (So only two data flow tasks can run concurrently);
  3. Enginethreads property 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)?

Currently using:

  • Progress Openedge db 11.3 to extract data from
  • Visual Studio 2015 for SSIS packages
  • SQL Server 2016 to load data into

Set precedence constraints between your data flow tasks so that they execute sequentially. https://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.