Post reply

Import millions row data from MySQL to MS SQL

  • October 12, 2015 at 4:05 am

    #304168

    Hi All,

    I am very new to learning SQL Server, now I have to project to import data from MySql to Sql data.

    I have data more less 11 millions row in MySQL database and data located in data center.

    I have tried using SSIS with data flow transfer data but failed. and also I have tried using import data wizard from SSMS and also failed.

    I set my connection time out is zero

    Error :

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on ADO NET Source returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

    is there anyway to import millions row efficiently ?

    Please advice

    Regards

    Henry

  • October 12, 2015 at 4:22 am

    #1832772

    Have you tried importing via linked server?

    https://thesqldev.wordpress.com/2014/07/02/importing-data-in-a-mysql-database-into-a-sql-server-database/

  • October 12, 2015 at 7:32 am

    #1832806

    Have you reviewed the data types?

    You could create a 'csv' file and import it through bulk insert or bcp.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 12, 2015 at 9:10 am

    #1832817

    This is how we have done it.

    Export from MySQL to CSV File

    Import from CSV File to SQL Server.

    All done via SSIS with the export being controlled by a process task calling a batch file which exports the data using a sql statement executed again the mysql instance.

    A few examples here :http://www.mysqltutorial.org/mysql-export-table-to-csv/

    Then import using a data flow task in the SSIS package using the csv file as a source.

    MCITP SQL 2005, MCSA SQL 2012

  • October 12, 2015 at 9:22 am

    #1832822

    just for fun, i grabbed the Ashley Madison database bittorrent, which is in the form of MySQL dumps, brought it into MYSQL, and then tried to import it into SQL Server 2014 via linked servers.

    I had to make a number of MySQL config changes to get it to import efficiently, and the entire disk subsystem was SSDs.

    Just one dump was taking HOURS, but stopping, changing config values i googled, and then restarting again,I had some barely acceptable config changes(and i'm no mysql expert) where i got it to the point where each of the five tables were taking about 45 minutes to an hour for each over a linked server (all local SSD drives/no network)

    so five files, essentially five plus hours for a pretty huge db.

    So based on my experience, i'd say dumping the data to CSV and importing via BULK INSERT/bcp/sqlcmd would be less painful.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 12, 2015 at 9:16 pm

    #1832902

    I will try using linked server.

    I already did trying to export to CSV using SSIS, MySQL Workbench, Navicat but all failed.

    Thanks guys

  • June 1, 2016 at 8:53 pm

    #1882706

    Yes, I do this on SQL server 2014 with a linked server and a SSIS package and it works very well. Just install the MySQL ODBC driver for SQL server on the SLQ server and add it to the ODBC connections with your MySQL login credentials.

    -CodeMan

  • June 2, 2016 at 2:04 am

    #1882738

    yes i have similar project , i used linked server and able to import huge records without any issue

Viewing 8 posts - 1 through 7 (of 7 total)

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