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

Question

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

stricknyn

SSCertifiable

Points: 6560
More actions
February 22, 2024 at 3:36 pm

#4383001
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

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

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

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246959 More actions · Answer 2

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

stricknyn SSCertifiable Points: 6560 More actions · Answer 3

Hi Phil, thanks for your response.

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

stricknyn SSCertifiable Points: 6560 More actions · Answer 4

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.

Phil Parkin SSC Guru Points: 246959 More actions · Answer 5

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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin