Post reply

error ole db 0x80040E4D - Acquire connection failed 0xC0202009

  • 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 🙂

  • August 11, 2008 at 3:42 am

    #855943

    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

  • August 11, 2008 at 6:45 am

    #856039

    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 🙂

  • August 11, 2008 at 9:20 am

    #856150

    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

  • August 11, 2008 at 9:37 am

    #856174

    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

  • August 11, 2008 at 9:38 am

    #856175

    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

  • August 11, 2008 at 11:02 am

    #856255

    the child packages execute in sequence

    you have alternatives...

    Thanks 🙂

  • August 11, 2008 at 11:48 am

    #856276

    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.

  • August 11, 2008 at 1:36 pm

    #856350

    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 🙂

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

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