SSIS - Error: Failed to acquire connection

  • A situation caused an error. I need to fix that.

    Error -

    [Execute SQL Task] Error: Failed to acquire connection "MyDBConnection". Connection may not be configured correctly or you may not have the right permissions on this connection.

    Situation -

    I got a package created in SSIS 2005 (32 or 64 bit, I don't know). When I imported the package into 2008, I got several warnings, which I ignored. I added some of my code to this package and then executed it. I now get the above error.

    How do I fix it ?

  • I got a package created in SSIS 2005 (32 or 64 bit, I don't know). When I imported the package into 2008, I got several warnings, which I ignored. I added some of my code to this package and then executed it. I now get the above error.

    You might check whether it's an OLEDB connection. In SSIS 2005, the native SQL Client for OLEDB connections is at 1. In SSIS 2008 it's SQL Client 10. When I've done similar upgrades, there were some connections that remained at 1 even after the automatic upgrade. They need to be changed to 10.

  • etl.laptop (10/15/2013)


    A situation caused an error. I need to fix that.

    Error -

    [Execute SQL Task] Error: Failed to acquire connection "MyDBConnection". Connection may not be configured correctly or you may not have the right permissions on this connection.

    Situation -

    I got a package created in SSIS 2005 (32 or 64 bit, I don't know). When I imported the package into 2008, I got several warnings, which I ignored. I added some of my code to this package and then executed it. I now get the above error.

    How do I fix it ?

    I'd start by double-checking the connection configuration and permissions. If you find a problem, perhaps you can post back with specific details.

    Note that packages are neither 32- nor 64-bit. But they can be run in 32- or 64-bit mode. A bit like Word really - a .docx is a .docx.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Phil Parkin (10/15/2013)


    I'd start by double-checking the connection configuration and permissions. If you find a problem, perhaps you can post back with specific details.

    I don't know which specific details I can post. Not sure where the problem lies. The permissions and configs seem okay.

    As an aside, I just found a config file that can be used to set this troublesome connection. I imported it, but I don't know how to use it. This file has the connection properties that I need. How do I use this file and make it work ?

  • I had a similar issue. I was setting up a job to execute a SSIS package that I previously added to the SSIS catalogue on that server.

    I solved it by going to the Job Step > Configuration > Connection Managers> For each connection I had to specify the Password and set RetainSameConnection = True.

    I'm not sure that the RetainSameConnection is necesarry

  • Also, i noticed in earlier BIDs versions, it often solves the issue (unless you really do not have permissions...) if you just delete the connection, and create a new one from scratch. Only if your error repeats after you drop and create connection manager several time then you know you surely have a connection parameters/permissions problem.

    Likes to play Chess

  • Note that packages are neither 32- nor 64-bit. But they can be run in 32- or 64-bit mode. A bit like Word really - a .docx is a .docx.

    Thanks, I never knew that!

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

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

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