從SQL Server到MySQL的ETL實現

標籤：show   roc   測試   mina   測試資料   date()   解決方案   inf   插入   

情境：

需要一個從SQL Server源表拉資料到MySQL目標表的SSIS ETL Package，本來一個簡單的Data Flow組件就可以解決的問題，然而SSIS 2014不支援在Data flow中使用ADO.NET connection作為MySQL desitination，運行時會報錯（做source connection使用沒有問題），換成ODBC connection可以成功，但load速度太慢。

在以下測試表中插入260908條測試資料，然後用ODBC做Data Flow的destination，load所有資料耗時43分27秒！！

 1 CREATE TABLE tblTest( 2 ID BIGINT NOT NULL IDENTITY(1,1), 3 Col1 NVARCHAR(30), 4 Col2 NVARCHAR(30), 5 Col3 NVARCHAR(30), 6 Col4 NVARCHAR(30), 7 Col5 NVARCHAR(30), 8 Col6 DECIMAL(10,4), 9 Col7 DECIMAL(10,4),10 Col8 DECIMAL(10,4),11 Col9 INT,12 Col10 DATETIME2 DEFAULT GETUTCDATE()13 )

　　

解決方案：

將源表通過SQL Server的bcp命令匯出為檔案，然後在利用MySQL的LOAD DATA LOCAL INFILE命令匯入目標表中。

 1 --在SQL Server中建立SP如下，用以被SSIS Package調用，將源表匯出至檔案 2 CREATE PROCEDURE usp_ExtracttblTest 3 AS 4 BEGIN 5  6 EXEC sp_configure 7 ‘show advanced options‘, 8 1; 9 10 RECONFIGURE;11 12 EXEC sp_configure13 ‘xp_cmdshell‘,14 1;15 16 RECONFIGURE;17 18 DECLARE @cmd NVARCHAR(4000); 19 20 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","‘;21 22 EXEC master..xp_cmdshell @cmd;23 24 END25 26 GO

SSIS Package需要兩個Execute SQL Task來實現整個ETL：

Execute SQL Task 1: 調用以上預存程序，將源表匯出為檔案，匯出260908條測試資料耗時1秒

Execute SQL Task 2: 用ADO.NET connection的方式串連到MySQL目標資料庫，SQL Statement內容如下。匯入所有測試資料耗時8秒

1 LOAD DATA LOCAL INFILE ‘C:/Test/tblTest.csv‘2 INTO TABLE tblTest3 CHARACTER SET utf84 FIELDS TERMINATED BY ‘,‘5 ESCAPED BY ‘\\‘6 LINES TERMINATED BY ‘\r\n‘7 (ID,Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10);

 改進後的ETL Package跑下來耗時總和僅需10秒！

從SQL Server到MySQL的ETL實現