Is there a such thing as connections being made too fast in a package?

  • Hi all,

    SSIS doing some quirky things and I wanted to run a scenario just too see if anyone else has experienced.  So I'm basically upgrading packages from 2014 to 2019.  The current project I'm upgrading has a mater package and probably like 100 child packages.

    The lower Dev environment I'm testing and deploying to of course has no where near the amount of data that production.  So while I'm testing and running the package in visual studio I'm getting random failures.  The failures appear to be connection related. ie:


    DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Staging_FH" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

     

    Like I mentioned earlier there isn't a lot of data so it runs really fast.  In production the amount and speed of tasks as happening as often since it's loading data.  I'm wondering if it's like hitting the connections to the DB too fast or something in the lower environment.  If I test the child package that failed on its own it runs fine.  Then when I run it again it could be a different child package the next time.  It's really weird.  I tried setting the connection to "RetainSameConnection = True" to see if that helps.   But still get it.

    I of course have to have a positive result before moving to production, but if this is a issue that is known and experienced in the lower environments I can at least explain that.

    Has anyone experience anything like this?  Any help or pointers on resolving would be greatly appreciated.

     

    Thanks,

     

    Strick

  • The error message you posted is not overly helpful.

    Usually (as the message itself suggests), a more specific and helpful error can be found before that one. Please take a look and see whether you can see anything that looks promising.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Are these packages running in series or parallel? Are they all hitting the same DB?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi Phil, thanks for your response.

    Yes they are running in parallel and are hitting the same DB (Different tables though).

     

     

  • To better clarify, not all 100 are running in parallel.  There success/completion constraints throughout, but as certain tasks are finishing there are definitely a lot of tasks that are hitting the same DB in parallel.

  • Sounds like it might be some sort of contention issue. You could try reducing the max degree of parallelism and see whether that helps.

    Did you take a look at the detailed error messages again?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply