SSIS Intermittent Failures
I'm presenting this in two sections, a primary challenge which relates to stability of our SSIS packages using a 6.9.8 MySQL Connector and a secondary challenge as it relates to attempting to find a fix with version 8.0.12.
Primary Challenge and Context:
We have been running SSIS with MySQL Connector/.NET 6.9.8 for a number of years. The drivers have been rock solid pointing at a MySQL 5.6 database. However, we have recently migrated to AWS and have been pointing those same packages at an Aurora MySQL 5.6 source. Unfortunately, we are now having issues with stability. At times we get SSPI connection error: ADO NET Source has failed to acquire the connection {7F853977-C6C6-4F2B-847E-247BEFE953C2} with the following error message: “A call to SSPI failed, see inner exception." At other times, we see a hang condition whereby the SSIS package hangs and will eventually die after the timeout period elapses (8 hours). We can see that the specific Session ID in SSMS Activity Monitor shows the session in an Async_network_IO wait state. When we look on the Aurora/MySQL side of the connection, it shows that the query is complete and results have been returned, but the connection is remaining open. Other (non-SSIS) systems are using this Aurora/MySQL 5.6 without issue. Therefore, while we're not sure exactly what is causing this intermittent behavior, we believe that the MySQL Connector may be relevant. Both the SSPI and connection hang isseus are intermittent - we have 500+ packages that run daily using identical patterns and it's different packages on different days at different times - and sometimes they run for days without issue. When we manually re-run after failure, they usually run successfully. The connection hangs occur in MySQL SSIS Source components that hold straightforward, single table SELECTs that are streamed into dataflows.
Has anyone encountered a similiar scenario and found a fix? Or have experience narrowing down an issue like this? There are an impressive number of bug fixes related versions of the MySQL Connector/.NET since version 6.9.8 and 8.0.12 so we were hoping that upgrading to a newer version might resolve our issue. Unfortunately, that resulted in a different issue described below.
Secondary Challenge:
In an attempt to remediate the issue above, we upgraded our MySQL Connector to 8.0.12. Upon testing, we ran into an issue with some of the Execute SQL Task patterns that use the ADO.NET connection type. We are running a SELECT query in the task and storing the resultset in a variable. If we set the resultset to None, the package executes correctly but we use the resultset for subsequent processing. The error that comes back is: "Failed to create an IDataAdapter object. This provider may not be fully supported with the Execute SQL Task. Error message 'Object reference not set to an instance of an object.'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly." I have found others posting on forums that have run into a similar issue with this and report that 6.9.X of the driver work for this pattern, while 6.10.X and 8.0.X do not. We have hundreds of packages that use this pattern, so rebuilding them to use OLEDB or ODBC would be very challenging... and committing to this work is made more complex as we don't actually know if the driver upgrade will resolve the original issue.
Has anyone found a workaround for this? Or is it a known defect slated for bug fix in a future release?