从SQL Server到MySQL的ETL实现

萨尔法保护

　　场景：
　　需要一个从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秒！！
　　

CREATE TABLE tblTest(　　

>
Col1 NVARCHAR(30),　　

Col2 NVARCHAR(30),　　

Col3 NVARCHAR(30),　　

Col4 NVARCHAR(30),　　

Col5 NVARCHAR(30),　　

Col6 DECIMAL(10,4),　　

Col7 DECIMAL(10,4),　　

Col8 DECIMAL(10,4),　　

Col9 INT,　　

Col10 DATETIME2 DEFAULT GETUTCDATE()　　

)　　

　　解决方案：
　　将源表通过SQL Server的bcp命令导出为文件，然后在利用MySQL的LOAD DATA LOCAL INFILE命令导入目标表中。
　　

　　
CREATE PROCEDURE usp_ExtracttblTest
　　
AS
　　
BEGIN
　　

　　
EXEC sp_configure
　　
'show advanced options',
　　
1;
　　

　　
RECONFIGURE;
　　

　　
EXEC sp_configure
　　
'xp_cmdshell',
　　
1;
　　

　　
RECONFIGURE;
　　

　　
DECLARE @cmd NVARCHAR(4000);
　　


　　
SET @cmd = 'bcp "SELECT>　　

　　
EXEC master..xp_cmdshell @cmd;
　　

　　
END
　　

　　
GO
　　

　　SSIS Package需要两个Execute SQL Task来实现整个ETL：
　　Execute SQL Task 1: 调用以上存储过程，将源表导出为文件，导出260908条测试数据耗时1秒
　　Execute SQL Task 2: 用ADO.NET connection的方式连接到MySQL目标数据库，SQL Statement内容如下。导入所有测试数据耗时8秒
　　

LOAD DATA LOCAL INFILE 'C:/Test/tblTest.csv'　　
INTO TABLE tblTest
　　
CHARACTER SET utf8
　　
FIELDS TERMINATED BY ','
　　
ESCAPED BY '\\'
　　
LINES TERMINATED BY '\r\n'
　　
(ID,Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10);
　　

　　改进后的ETL Package跑下来耗时总和仅需10秒！