SSIS package load data from SQl to Mysql

Question

Post reply

SSIS package load data from SQl to Mysql

Bobby Glover

SSCarpal Tunnel

Points: 4143
More actions
August 13, 2012 at 5:45 am

#260682

Try to create a SSIS package to insert data from SQL to Mysql.
The setup is as follows
OLE DB connection to SQL db
ADO.net connection to Mysql.
Both works fine I can select from the MYSQL db using the connection manager.
I have setup a step to gather the data from SQL and dump it to a temp table. Trying to insert it to the MYSQL destination is the problem.
The problem I face is that when I try and select my destination table (mysql) I can’t get a list of tables via ado.net destination workflow connection. ‘No tables or views could be loaded’.
How do I select the table I want to insert into in MYSQL using SSIS.
August 13, 2012 at 8:37 am

This was removed by the editor as SPAM
August 13, 2012 at 9:15 am

This was removed by the editor as SPAM
August 15, 2012 at 6:24 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 1

No I used the ODBC version. I just read that I need my source to also use ADO.Net.

http://dbperf.wordpress.com/2010/07/23/sql-server-integration-services-2008-ssis-and-mysql/

When I try to map the MYSQL table I have to create one everytime, even when I do create one. I can never remap it again. So I don't see how this package could ever run as an hourly task id this solution only used as a 1 off.

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 2

Thanks I will try that, I do need all destination tables to be visable.

Again Thanks.

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 3

Is this available for SQL 2008 as I can't get the connection to work.

When I type in servername,username and password. the initial catalog is greyed out.

Using VS 2008.

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 4

Almost there by following

http://www.packtpub.com/article/mysql-data-transfer-using-sql-server-integration-services-ssis

I can get the data to MYSQL but in have to create a table when creating the SSIS package. It doesn't recognise any of the existing MYsql tables and will not populate them in a drop box.

I want to transfer data to an existing table is it possible?

Gazareth One Orange Chip Points: 27737 More actions · Answer 5

Is it an option to change your data access method to 'SQL command' rather than 'Table or View', and write the insert statement yourself?

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 6

Not for the destination table, has to be a table or view.