none
Failed to acquire connection error RRS feed

  • Question

  • Greetings!

    In making a new SSIS package that is based off an existing working package, I had read about rolling back transactions when using Sequence Containers so that the state of a database can be retrieved, should an error occur within the container.

    As I understand it, the outermost object's TransactionOption should be set to "Required" and all the obects within it should be set to "Supported" in order to achieve this roll-back capability.

    However, after finally narrowing an error I was receiving down to this particular setting, I discovered that, if I set TransactionOption to "Required," my package fails with the error, "Failed to acquire connection 'Connection'. Connection may not be configured correctly or you may not have the right permissions on this connection."

    But, if I set the container to "Supported," it works fine.

    So, I have two questions;

    1. Is my understanding what I'm trying to do? Does this TransactionOption actually do what I think it does?
    2. If it does, what am I doing wrong?

    Hmm. I guess that's actually three. Ennywhoo...

    The test package is a ForEach Loop container with the Sequence Container within it and a simple Execute SQL Task within that, as the image shows;

    Now, if I set the TransactionOption of either the ForEach Container or the Sequence Container to "Required," the package fails with the above mentioned error. But, if I make the setting, "Supported," it works. But, if it's set to "Supported," then, as I understand it, the roll-back characteristic I'm after is lost.

    Am I doing this right? (Question #4!)

    Thanx in advance for any help!

    Tuesday, February 2, 2016 8:45 PM

Answers

  • OK, found the answer - I think.

    I enabled MSDTC on my machine, but discovered that it has to be active on the server, too, as I'm not running a local database.

    So, I put in a request for that and we'll see how it turns out.

    But, I suspect that's the problem.

    • Marked as answer by Adam Quark Wednesday, February 3, 2016 9:34 PM
    Wednesday, February 3, 2016 9:34 PM

All replies

  • Oh! Follow-up - the same error occurs if I remove the Sequence Container and place the SQL task within the ForEach on its own.
    Tuesday, February 2, 2016 8:46 PM
  • OK, found the answer - I think.

    I enabled MSDTC on my machine, but discovered that it has to be active on the server, too, as I'm not running a local database.

    So, I put in a request for that and we'll see how it turns out.

    But, I suspect that's the problem.

    • Marked as answer by Adam Quark Wednesday, February 3, 2016 9:34 PM
    Wednesday, February 3, 2016 9:34 PM
  • Hi Adam,

    Thank you for sharing the answer, it might be beneficial to other community members. Your cooperation and fully support are highly appreciated.


    Sam Zha
    TechNet Community Support

    Friday, February 5, 2016 1:49 AM