5

I am getting following error while attempting to execute the package. I can execute individual task successfully. But whenever attempt to execute whole package it gives me error. SSIS Data flow on distributed transaction - [Execute SQL Task] Error: Failed to acquire connection "targetCon2". Connection may not be configured correctly or you may not have the right permissions on this connection.

I went through many links but nothing worked for me so starting a new thread. I checked SQL Agent Job - Connection may not be configured correctly or you may not have the right permissions on this connection?

SSIS package fail due to 'acquire connection' error

SSIS Package failing with "Failed to acquire connection" error

My distributed transaction service is running. DTC settings are correct. I am executing everything sequentially. Don't know whats wrong. I want to execute whole part in one transaction.

Please check the screenshots for more details. Project Snap and Error

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
Pavvy
  • 353
  • 3
  • 6
  • 15
  • Right click on `targetcon2` - go to edit - at bottom left, click test connection. Is it connecting ? – Prabhat G Jul 07 '17 at 12:27
  • Yes..it's connecting. I'm able to execute single task. – Pavvy Jul 07 '17 at 12:42
  • did you try this link's solution : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0a14ea20-ceac-46af-aa5c-3dd055f1da6b/error-unable-to-enlist-in-the-transaction-in-ssis-package-with-oracle-11g?forum=sqlintegrationservices – Prabhat G Jul 07 '17 at 13:05
  • Also try once by changing `TransactionOption` to 'required' – Prabhat G Jul 07 '17 at 13:07
  • Yes @PrabhatG I have tried. Nothing happened. – Pavvy Jul 07 '17 at 13:22
  • @Pravvy For the DropForeignKeys, post the screenshot of the Execute SQL Task editor and it's associated connection manager. – J Weezy Aug 21 '18 at 18:39

1 Answers1

0

I just experienced this issue as well. My problem was i had switched out the params file to point to a new environment. While the ADO connection 'test connection' button worked; when running the package it failed. I rebuilt the soution and it worked great after that.

Michael
  • 1
  • 2