1

I am having trouble getting an SSIS package to work in ADF. The package runs from Visual Studio & also from the SSIS Catalog on my on premise server. The package has the following steps:

  1. Pulls data from an external OData feed (SAP by Design).
  2. Loads the data into an Azure SQL Server staging database on a DBaaS instance.
  3. Loads the same data from Staging to a Datawarehouse on the same DBaaS instance.

Steps I have taken:

  1. Created the SSISDB on my Azure instance.
  2. Created an Azure Data Factory.
  3. Created an Azure-SSIS Integration Runtime.
  4. Created & tested a simple package to copy data from Staging to the Datawarehouse.
  5. Deployed a working SSIS package to Azure.
  6. Created an External IR.

The package execution on fails validation with this error: Error: Cannot acquire a managed connection from the run-time connection manager.

Obviously, I am missing some crucial element in my set up but haven't been able to figure it out. I am, of course, very new to Azure. Any help would be greatly appreciated. :)

Marvel

Marvel
  • 21
  • 4
  • 2
    Does this answer your question? Check Hadi's excellent info [How to fix "\[OData Source \[56\]\] Error: Cannot acquire a managed connection from the run-time connection manager." error in SSIS?](https://stackoverflow.com/questions/54709366/how-to-fix-odata-source-56-error-cannot-acquire-a-managed-connection-from) – digital.aaron Feb 21 '20 at 00:27
  • I don't believe that is a duplicate because the poster indicates it already works on-prem – Nick.Mc Feb 21 '20 at 06:19
  • Where is the OData source? is it public or is it on your internal network? How are you authenticating? Have you connected your Azure-SSIS runtime to a VNET? Can you also dig through the SSIS logs and see if you can find a more detailed error? – Nick.Mc Feb 21 '20 at 06:20
  • The Odata source is a cloud-based ERP system - SAP by Design using an authentication account/password provided by the vendor. The authentication type in the packages is Basic. We use a SQL Server login to connect to the Azure databases. – Marvel Feb 21 '20 at 13:36
  • It looks as if this question has been closed however the solution that was linked to is for an on premise issue, not an issue running packages in Azure SSISDB to pull data from an external source. – Marvel Feb 21 '20 at 14:34
  • I've voted to reopen. Please do your best to find more details in the SSIS log. In the meantime I actually suggest that if it is as simple as you describe, please try rebuilding it in native Azure data factory. It's _way_ less painful than having to edit SSIS in visual studio, deploy, get an Azure-SSIS runtime working, fix connectivity and driver issues..... – Nick.Mc Feb 22 '20 at 06:20

0 Answers0