I'm attempting to pinpoint the cause of repeated connection failures when using SSIS to update an AWS RDS MySQL instance using ODBC. They appear to be at random and not correlated with heavy use based on Performance Insights. I've been tasked with switching them all over to ADO.NET connections and its been one battle after another. I finally have all the drivers installed and both ADO Source and Destination tools can list the tables using the built in connection managers. I can successfully use the connection when executing an "Execute SQL Task" from the control flow.
The problem starts when i attempt to use the built in ADO.Net source and Destination tools. Both list all of the tables using the exact format "schema"."table_name" and I believe this is the problem. When i drag an ADO.NET source into the data flow i select my ADO.NET connection, use the drop down to select my table but when i select columns on the left hand side i receive "You have an error in your sql syntax. Check the manual that corresponds to your MySQL server version.