Here I am, day three... attempting to sync a data view on a Windows Vista box (64 bit) running MSSQL 2012 and Visual Studio 2010. Sanity is slipping and hunger for progress fills my attention. I went through hell trying to get the MySQL ODBC drivers to get the job but to no avail...everyone seems to be lost and all the threads I can find are solutions that do not work for me. The problem: System DSN's not being seen by SSIS. http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/7a769014-a412-417f-b0b5-b082fb6649f8 I make the decision to try out the ADO.NET connector...and to my surprise it is actually in the selection list in data sources in SSIS. So I take off running to create a Data Flow Task, create an ADO.NET Source (a local MSSQL DB)...all is good as usual. Then I move swiftly to creating a ADO.NET Destination, enter my credentials...wow, I am selecting a database finally on my linux server! Happy thinking that I finally have figured a way to get the job done. Then I move to mappings...nope, something is wrong...I am getting an error that hurts my eyes: Pipeline component has returned HRESULT error code 0xC0208457 from a a method call. Error at Data Flow Task [ADO NET Destination [81]]: Failed to get properties of external columns. The table name you entered may not exist or you do not have SELECT permission on the table object and an alternative attempt to get column properties through connection has failed. Detailed error messages are" 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 "database".tablename" at line 1. The descriptor files on path C:\Program Files (x86)\Microsoft SQL Server\110\DTS\ProviderDescriptors\ does not contain schema information for connection of type MySQL.Data.MySqlClient.MySqlConnection. So it looks like it can't the information and therefore I cannot map the tables properly. Any ideas on this would be ultra helpful...thanks in advance to All!Michael

I have used this connector before to query data from a MySQL Database http://dev.mysql.com/downloads/connector/net Make sure that you have closed down your BIDS or SQL Server Data Tools before installation. Once you have installed go to create your ADO NET Source and you should see an option for MySql Data Provider NOTE: just remember that this runs as a 32bit(x86) so make sure you change your package configuration for Run64BitRunTime to false. And if you deploy it to your SSIS Server, SQL Server 2008 and earlier or SQL Server 2012 go into the Step Properties for your job, then under Configuration click on Advanced and then put a tick for 32-bit runtime. I hope that it helps TEst

I think I have found the solution...although I am not clear as to how to execute it correctly. Create an ADO.NET Connection Manager which uses the ODBC driverSet the connection managers RetainSameConnection property to TrueAdd an Execute SQL Task before your data flow to set the SQL_MODE Ex. set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES'Make sure that your Execute SQL Task and your ADO.NET Destination are using the same connection manager. From this link: http://blogs.msdn.com/b/mattm/archive/2009/01/07/writing-to-a-mysql-database-from-ssis.aspx I am confused though as the instructions were written in 2009. Adding an Execute SQL Task before my "data flow" ...does that mean in the "Control Flow" tab? Or in the "Data Flow" tab? Wondering also if it would be ok to change the SQL mode on the MySQL server? However I am a little afraid that will cause a problem in the future? Michael

I think I have found the solution...although I am not clear as to how to execute it correctly. Create an ADO.NET Connection Manager which uses the ODBC driver Set the connection managers RetainSameConnection property to True Add an Execute SQL Task before your data flow to set the SQL_MODE Ex. set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES' Make sure that your Execute SQL Task and your ADO.NET Destination are using the same connection manager. From this link: http://blogs.msdn.com/b/mattm/archive/2009/01/07/writing-to-a-mysql-database-from-ssis.aspx I am confused though as the instructions were written in 2009. Adding an Execute SQL Task before my "data flow" ...does that mean in the "Control Flow" tab? Or in the "Data Flow" tab? Wondering also if it would be ok to change the SQL mode on the MySQL server? However I am a little afraid that will cause a problem in the future? Michael It means: In Control Flow tab; add an execute sql task and add a data flow task after that.http://www.rad.pasfu.com

Doesn't seem that I would need to set a session sql mode ...I already set the global sql mode to: 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI' Although this mans suggestion calls for the sql mode: sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES' Would this matter? I am struggling badly on this...keeping me awake and away from other tasks. All help is most appreciated. Michael

Very frustrating...would love to have this behind but still there is a tree in the middle of the road and I cannot proceed. To answer question...I definitely have tried several times with all methods: Error when using an ODBC Destination: SSIS package "c:\users\administrator\documents\visual studio 2010\projects\Integration Services Project13\Integration Services Project13\Package.dtsx" starting. Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning. Error: 0xC0014020 at Package, Connection manager "mysql-syslink.nuser": An ODBC error -1 has occurred. Error: 0xC0014009 at Package, Connection manager "mysql-syslink.nuser": There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server. Error: 0x20F at Data Flow Task, ODBC Destination [17]: The AcquireConnection method call to the connection manager mysql-syslink.nuser failed with error code 0xC0014009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: ODBC Destination failed validation and returned error code 0x80004005. Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation. Error: 0xC0024107 at Data Flow Task: There were errors during task validation. SSIS package "c:\users\administrator\documents\visual studio 2010\projects\Integration Services Project13\Integration Services Project13\Package.dtsx" finished: Failure. Error when using ADO.NET: SSIS package "c:\users\administrator\documents\visual studio 2010\projects\Integration Services Project13\Integration Services Project13\Package.dtsx" starting. Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning. Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning. Error: 0xC020844B at Data Flow Task, ADO NET Destination [2]: An exception has occurred during data insertion, the message returned from the provider is: Unknown column 'p1' in 'field list' Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "ADO NET Destination" (2) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (9). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning. Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "ADO NET Destination" wrote 0 rows. Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning. Task failed: Data Flow Task Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "c:\users\administrator\documents\visual studio 2010\projects\Integration Services Project13\Integration Services Project13\Package.dtsx" finished: Failure. And last but not least ADO.NET destination using a System DSN created from the odbcad32.exe in the SysWOW folder: SSIS package "c:\users\administrator\documents\visual studio 2010\projects\Integration Services Project14\Integration Services Project14\Package.dtsx" starting. Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning. Error: 0xC0208452 at Data Flow Task, ADO NET Destination [2]: ADO NET Destination has failed to acquire the connection {24E82E24-D7F9-499C-B761-F683F62618FF} with the following error message: "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: ADO NET Destination failed validation and returned error code 0xC0208452. Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation. Error: 0xC0024107 at Data Flow Task: There were errors during task validation. SSIS package "c:\users\administrator\documents\visual studio 2010\projects\Integration Services Project14\Integration Services Project14\Package.dtsx" finished: Failure. Michael

Michael, did you ever get it to work?

hi Mike8Mike Will you share the solution of this probelm .. because i'm facing the exact error . http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/a1ec2343-ea5a-419e-b5b2-5c1e54ecede0 Kindly help me.