Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1798
  • Last Modified:

Using SSIS 2008 to load data from MSSQLServer to MySQL

Hi,
I'm trying to moving data from MSSQlserver 20008 to MySQL 5.0.81 using SSIS 2008. The package is meant to be something which is re-run on a regular basis (probably daily or weekly). As part of the package is a stored proc which produces rows of a table which is then written to the MySQL table. Essentially the error I'm getting is:

Error: 0xC020844B at Get New Time Periods to load into FluID, ADO NET Destination [61]: An exception has occurred during data insertion, the message returned from the provider is: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.28-rc-log]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 '"TIME_PERIODS" ("PERIODICITY_TYPE_ID", "TIME_PERIOD_NAME", "YEAR", "MONTH", "WEE' at line 1
Error: 0xC0047022 at Get New Time Periods to load into FluID, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "ADO NET Destination" (61) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (64). 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.

The connection to the MySQL db is using .Net/ODBC Data provider and I've also tried .Net/MySQL provider too, pretty much with the same error. The properties for both these connections use the defaults although I've also changed the RetainSameConnection property to True when looking at some of the other "similar" issues in the knowledge base. One is particular was to prefix the insert with an Execute SQL Task which runs
SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

However this doesn't seem to make much difference either.

Any ideas ?
0
JohnRawlo
Asked:
JohnRawlo
  • 3
1 Solution
 
Jason Yousef, MSSr. BI DeveloperCommented:
Hi,

I don't think the error is regarding the connection..Also you can check That link

Does the SP works fine from inside SSMS?  I think it's a query problem, can you post your quesry and Mysql Version?
0
 
JohnRawloAuthor Commented:
Hi there,
The link you mentioned was one of the ones I used to try either the Net/ODBC Data provider or the .Net/MySQL provider = they both gave the same result.
As for the MSSQL stored procedure that's pretty straight forward - works fine within and outside SSIS. I've even tried from a table instead of an sp with the same result.
The actual MSSQL server version is 2000 (although SSIS 2008 is being used) and the MySQL server version is 5.0.81.
0
 
Reza RadCommented:
seems that your connection is ODBC, am I right?
if yes , try with .NET provider , download it from here:
http://dev.mysql.com/downloads/connector/net/5.1.html

also this is good reference to how to connect to mysql from SSIS:
http://blogs.msdn.com/b/mattm/archive/2008/03/03/connecting-to-mysql-from-ssis.aspx

there are two data providers for mysql, ODBC and .NET, try each of them,sometimes just one of these methods works.
0
 
JohnRawloAuthor Commented:
Thanks for that. The later version of the ODBC connector seemed to fix things.
Now I've got some other problems but I'll see if I can get through those.

Thanks once again.
0
 
JohnRawloAuthor Commented:
The updated ODBC connector (i.e. the solution) certainly helped advance the SSIS package. Now onto the other problems.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now