Hourly data sync from local MSSQL 2005 to AWS mySQL

[Chris Barr]

We have a local membership database on MSSQL 2005 that needs to update the AWS mySQL DB hourly. Historically we've done this with an SSIS package and a "Data Flow Task" I'd think that we could continue to do the same. There are issues though.
I've tried ADO and OLE connections, locally and directly, they all throw errors.

I've been focusing on a direct ADO connetion recently to try to get everything running and the first main error was this
Error: 0xC020844B at Export <originating table>, ADO NET Destination [465]: An exception has occurred during data insertion, the message returned from the provider is: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"<col1 name>", "<col2 name>", "<col3 name>", "<col4 name>", "<col5 name>", "<col6 name>", "<col7 name>", "<col8 name>' at line 1

I was able to resolve this by first running a basic "SQL task" and setting the sql_mode to ANSI before the data transfer
set sql_mode='ANSI'

How I am supposed to set the entire DB to ANSI, I have no idea since I need "super" access but can't seem to get that access level. This is a mySQL instance in AWS, so it seems as though there are some restrictions there.

The error is now this and I can't seem to get past it no matter what I try.
Error: 0xC020844B at Export <originating table>, ADO NET Destination [465]: An exception has occurred during data insertion, the message returned from the provider is: Unknown column 'p1' in 'field list'

Even though the membership database is a 2005 DB, I have created the SSIS package in VS2008 so that I can create the ADO source and destinations.

I understand that there is an ADO issue going to mySQL, but the ODBC connectors outright fail and cannot connect, and beyond that, an ODBC destination does not exist in SSIS packages.