从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秒！！
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_configure 13 '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 END 25 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 tblTest 3 CHARACTER SET utf8 4 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秒！
