Using SSIS 2008 to load data from MSSQLServer to MySQL

Posted on 2011-05-12
Last Modified: 2013-11-10
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

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

Any ideas ?
Question by:JohnRawlo
    LVL 21

    Expert Comment


    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?

    Author Comment

    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.
    LVL 30

    Accepted Solution

    seems that your connection is ODBC, am I right?
    if yes , try with .NET provider , download it from here:

    also this is good reference to how to connect to mysql from SSIS:

    there are two data providers for mysql, ODBC and .NET, try each of them,sometimes just one of these methods works.

    Author Comment

    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.

    Author Closing Comment

    The updated ODBC connector (i.e. the solution) certainly helped advance the SSIS package. Now onto the other problems.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how the fundamental information of how to create a table.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now