ETL implementations from SQL Server to MySQL

Scene:

An SSIS ETL package that pulls data from a SQL Server source to a MySQL target table needs to be solved by a simple data flow component, but SSIS 2014 does not support the use of ADO in Data flow Connection as MySQL desitination, the runtime will error (do not use the source connection), replaced by ODBC connection can be successful, but the load speed is too slow.

Insert the 260908 test data into the following test table, and then use ODBC to do data flow destination,load all that time takes 43 minutes 27 seconds !!

1 CREATE TABLEtbltest (2IdBIGINT  not NULL IDENTITY(1,1),3Col1NVARCHAR( -),4Col2NVARCHAR( -),5Col3NVARCHAR( -),6Col4NVARCHAR( -),7Col5NVARCHAR( -),8Col6DECIMAL(Ten,4),9Col7DECIMAL(Ten,4),TenCol8DECIMAL(Ten,4), OneCol9INT, ACol10 DATETIME2DEFAULTgetutcdate () -)

　　

Solution:

Export the source table to a file via the SQL Server BCP command, and then import the target table using the MySQL load DATA LOCAL infile command.

1 --Create an SP in SQL Server as follows to be called by the SSIS package to export the source table to a file2 CREATE PROCEDUREusp_extracttbltest3  as4 BEGIN5 6 EXECsp_configure7 'Show advanced Options',8 1;9 Ten RECONFIGURE; One  A EXECsp_configure - 'xp_cmdshell', - 1; the  - RECONFIGURE; -  - DECLARE @cmd NVARCHAR(4000);  +  - SET @cmd = 'bcp "Select Id,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10 from Test: Tbltest "queryout c:/test/tbltest.csv-s" SQL Server IP "-U" DBUser "-P" Dbuserpassword "-c-c" 65001 "-T", "'; +  A EXECMaster.. xp_cmdshell@cmd; at  - END -  - GO

The SSIS package requires two execute SQL tasks to implement an entire ETL:

Execute SQL Task 1: Call the above stored procedure, export the source table as a file, export 260,908 test data takes 1 seconds

Execute SQL Task 2: Connect to the MySQL target database in ADO connection, and the SQL statement content is as follows. Importing all test data takes 8 seconds

1 LOADDATA LOCAL INFILE'C:/test/tbltest.csv'2  into TABLEtbltest3 CHARACTER SETUTF84Fields TERMINATED by ','5Escaped by '\\'6LINES TERMINATED by '\ r \ n'7(ID,COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10);

The improved ETL package takes only 10 seconds to run down the combined time!

ETL implementations from SQL Server to MySQL