SQL Server Integration Services (SSIS) package execution can always throw up a few spanners, particularly when it comes to the task of deploying packages out to a SQL Server SSISDB catalog - principally, a specialised database for the storage of .dtsx packages, execution settings and other handy profile info to assist with automation. Problems can generally start creeping if you decide to utilise non-standard connectors for your package data sources. For example, instead of employing the more oft utilised Flat File Connection Manager for .csv file interaction, there may be a requirement to use the Excel Connection Manager instead. While I would generally favour the latter data Connection Manager where possible, the need to handle .xlsx file inputs (and to output into this file format) comes up more often than you might think. Bearing this in mind, it is, therefore, always necessary to consider the impact that deploying out what I would term a “non-standard Connection Manager” (i.e. a non-Flat File Connection Manager) can have for your package release. Further, you should give some serious thought towards any appropriate steps that may need to be taken within your Production environment to ensure a successful deployment.
With all of this in mind, you may encounter the following error message when deploying out a package that utilises the ADO.NET Connector for MySQL - a convenient driver released by Oracle that lets you connect straightforwardly with MySQL Server instances, Ã la the default SQL Server ADO.NET connector:
Error: Microsoft.SqlServer.Dts.Runtime.DtsCouldNotCreateManagedConnectionException: Could not create a managed connection manager. at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection
Specifically, this error will appear when first attempting to execute your package within your Production environment. The good news is that the reason for this error - and its resolution - can be easily explained and, with minimal effort, resolved.
The reason why this error may be happening is that the appropriate ADO.NET MySQL driver is missing from your target SSISDB server. There is no mechanism for the proper dependent components to be transported as part of deploying a package to a catalog, meaning that we have to resort to downloading and installing the appropriate driver on the server that is executing the packages to resolve the error. Sometimes, as part of long development cycles, this critical step can be overlooked by the package developer. Or, it could be that a different individual/team that is responsible for managing deployments are not necessarily well-briefed ahead of time on any additional requirements or dependencies needed as part of a release.
For this particular example, getting things resolved is as simple as downloading and installing onto the SSISDB Server the latest version of the MySQL Connector Net drivers that can be found on the link below:
If you find yourself in the same situation not involving the above Data Connector, then your best bet is to interrogate the package in question further and identify the appropriate drivers that are needed.
Now, the key thing to remember about all of this is that the driver version on the client development machine and the SSISDB server needs to be precisely the same. Otherwise, you will more than likely get another error message generated on package execution, resembling this:
Could not load file or assembly ‘MySql.Data, Version=6.10.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference.
In which case, you will need to resolve the version conflict, ideally by assuring that both machines are running the latest version of the corresponding driver. An uninstall, and server reboot could be necessary at this juncture, so be sure to tread cautiously.
SSIS development can often feel like a protracted, but ultimately worthwhile, process. With this in mind, it is natural to expect some bump in the roads and for potentially essential steps to be overlooked, particularly in larger organisations or for more complex deployments. Putting appropriate thought towards release management notes and even dedicated testing environments for deployments can help to mitigate the problem that this post references, ensuring a smooth pathway towards a prosperous, error-free release 🙂