SSIS Package in SSMS - ADO NET Source has failed to acquire the connection - Could not create a managed connection manager

1
votes

I have some problems with getting my SSIS Packages to work from my Azure SQL SSIS DB in Azure.

I built the SSIS packages in VS and they run without any problem. What i am doing is getting data from a remote MariaDB (Source) with a ".NET MySQL Data Provider (mysql-connector-net-8.0.15)" and inserting the data in a Azure SQL Database in Azure.

When i run the task/package in VS it works without any problems but as soon as i have deployed the SSIS project to my SSIS DB in azure i get this error message:

ADO NET Source has failed to acquire the connection {185AE7A1-B27B-428C-A60F-FD**********} with the following error message: "Could not create a managed connection manager.".

I have been searching the net on what can cause the problem and i've found everything from the 64-32bit incompatibiluty wich i have tried to turn off completely.

enter image description here

Though when i try to tick and run the task in 32-bit mode in SSMS i get this error:

enter image description here

I have also downloaded the latest MySQL .Net Data provider (mysql-connector-net-8.0.15) from their homepage, though i don't know how to check if Azure SQL server has the same provider implemented in Azure (if there even is one...)

I'm totally lost here..if there is anything else the person who reads this needs for he/she to help out just ask and i'll try to provide the necessary information.

1
mysqlssisazure-sql-databasessmsdataprovider
sounds like a dependency missing server side. When you say "SQL SSIS DB in Azure" is that a VM running integration services you're deploying to? Or is this a managed instance? – Tim Mylott

1 Answers

2
votes

Well i found the solution to my problem. As its been pointed out there surely was something missing in the server, the server being an Azure SQL Server in relation to Azure Data Factory.

The main reason for my problem was a missing .Net provider for MySQL not being installed by default for my Integration Runtime (if i've understood this correctly) for Azure Data Factory.

So i Followed this guide:

Customize setup for the Azure-SSIS integration runtime

I installed the latest MySQL-net-connector-8.0.15.0.msi and voila, it worked!

As Tim Mylott in a previous comment mentioned, the same principle should apply to on-prem setup.

Hope this helps out anyone encountering this in the future.