error ole db 0x80040E4D - Acquire connection failed 0xC0202009

Question

error ole db 0x80040E4D - Acquire connection failed 0xC0202009

diego.piccolboni

Mr or Mrs. 500

Points: 508
More actions
August 9, 2008 at 1:37 am

#123817

Hello,
I have a problem with the execution of a package in SSIS.
This package has 12 "execute package task" (children package).
The passwords for the connection are passed through variables.
The children packages work, while the main package fails when scheduled with
sql server agent...
I get the following errors:
An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Accesso non riuscito per l'utente 'sa'.".
The AcquireConnection method call to the connection manager "xxxx.sa" failed
with error code 0xC0202009
The errors are random and occur for one or two packages so causal... :crying:
someone is able to help, there would be very grateful 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

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

hodgy SSCertifiable Points: 5906 More actions · Answer 1

does the variable for the connection in the parent package have the same name as the variables in the child packages?

if this is the case, then when the child package variable is changed then the parent package variable is also updated. if the parent connection credentials are different then you could get an access error.

to fix it, either change the variable names or their namespaces. by default, the variables in both the parent and child packages will be in the "user" namespace. perhaps, change the parent variable namespace from "user" to "parent".

tom

Life: it twists and turns like a twisty turny thing

diego.piccolboni Mr or Mrs. 500 Points: 508 More actions · Answer 2

Yes, variables have the same name.

in the child package I have used this procedure to create variables

SSIS -> Package Configuration -> Add Parent Package Variable (password connection manager)

while in the parent package I created variables manually with the password for the connection.

the variables are initialized in the parent package and the value does not change

if you have other recommendations tell me well

Thanks 🙂

hodgy SSCertifiable Points: 5906 More actions · Answer 3

Do the child packages execute in sequence, or do you have them executing in parallel?

Life: it twists and turns like a twisty turny thing

Chinna SSCertifiable Points: 5408 More actions · Answer 4

I believe you should change the max executable threads to 12 in the package properties ...

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

Chinna SSCertifiable Points: 5408 More actions · Answer 5

http://blogs.msdn.com/sqlperf/archive/2007/05/11/implement-parallel-execution-in-ssis.aspx

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

diego.piccolboni Mr or Mrs. 500 Points: 508 More actions · Answer 6

the child packages execute in sequence

you have alternatives...

Thanks 🙂

Sarang Bobde Mr or Mrs. 500 Points: 534 More actions · Answer 7

For packages those are failing, check "ProtectionLevel" property

Also Check connection pooling for SQL server. I guess The connection is not getting realeased after execution of each package.

diego.piccolboni Mr or Mrs. 500 Points: 508 More actions · Answer 8

protection is equal for all packages

(default value: EncryptSensitiveWithUserKey)

Connection Manager -> All -> Pooling OLE DB Services

(default value: default)

Server

Maximum number of concurrent connections (0 = unlimited)

I have to change something?

Thanks 🙂