SSIS数据同步实践

背景

在已初步验证不同实例下同构表数据同步方案之后，为了实现数据持续同步，需使用SSIS把之前的生成脚本和执行脚本的两个步骤组合在一起部署成包之后，通过JOB定时去执行；

测试环境

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor)

SSIS开发工具

SSDT 15.8.9

SSDT需格外安装，这个好像从sql server 2012开始就需格外安装了。

测试步骤

1).先配置好链接数据库信息，新建链接，分别是联azure sql database 和本地sql server;如下图

2).新建文件系统任务，目的是重复执行的时候删除对应文件夹下的已生成的脚本文件，双级文件系统任务配置如下

3).执行SQL任务，该任务的目的是为了生成比对的同步脚本文件，配置如下

对应的处理逻辑脚本如下，有需要可以根据需求，修改成批量或者处理个别表；

DECLARE @TableNames AS TABLE
(
    id INT IDENTITY(1, 1),
    tableName VARCHAR(100)
);

DECLARE @sTableDiff NVARCHAR(1000);
DECLARE @tableName VARCHAR(100);
DECLARE @counter INT;
DECLARE @maxCount INT;

INSERT INTO @TableNames
SELECT a.name
FROM sys.sysobjects a
    INNER JOIN
     (
         SELECT object_id
         FROM sys.columns
         WHERE is_rowguidcol = 1
               OR is_identity = 1
         UNION
         SELECT object_id
         FROM sys.indexes
         WHERE index_id = 1
     ) b
        ON a.id = b.object_id
WHERE a.type = 'U' --and a.name='TMonStoreCheck';

SET @counter = 1;

SELECT @maxCount = COUNT(name)
FROM sys.sysobjects a
    INNER JOIN
     (
         SELECT object_id
         FROM sys.columns
         WHERE is_rowguidcol = 1
               OR is_identity = 1
         UNION
         SELECT object_id
         FROM sys.indexes
         WHERE index_id = 1
     ) b
        ON a.id = b.object_id
WHERE a.type = 'U' --and a.name='TMonStoreCheck';

WHILE @counter <= @maxCount
BEGIN
    SELECT @tableName = tableName
    FROM @TableNames
    WHERE id = @counter;
    SELECT @sTableDiff = '';

    SET @sTableDiff
        =  ' "C:Program FilesMicrosoft SQL Server130COM	ablediff.exe" -sourceserver  [faqb6n86e4.database.chinacloudapi.cn]  -sourceuser  ymjj -sourcepassword  ***** -sourcedatabase xw_dl_1009676_01 -sourcetable ' + @tableName + ' -destinationserver [butt-joint] -destinationuser sa -destinationpassword ****** -destinationdatabase xw_dl_1009676_01 -destinationtable '+@tableName+'  -f F:4_9	est'+@tableName

    EXEC master..XP_CMDSHELL  @sTableDiff
    SET @counter = @counter + 1;
END;

为了流更清晰，我们可以选中【文件系统任务】和【执行SQL任务】分组；

4).Foreach 循环容器，目的是逐个去读取文件夹中的文件名称；

5).执行脚本通过foreach 循环容器的传参去执行已生成好的差异脚本；

插入脚本如下：

declare @temp nvarchar(200)
select @temp ='"F:Program FilesMicrosoft SQL ServerClient SDKODBC130ToolsBinnsqlcmd.exe" -a 32576 -S butt-joint -d xw_dl_1009676_01  -i F:4_9	est'+ ? +'  -f 65001'
EXEC master..xp_cmdshell @temp

注意：

可能需要安装NET Framework 3.5, 否则sqlcmd无法正常执行；
sqlcmd 的前期测试，为了方便问题定位可以使用 -o 选项把错误信息打印出文本；
sqlcmd 后面切记加 -f 65001,避免中文在数据库中插入后，select显示乱码的情形；
当插入的脚本文件非常大比如超100M+的时候建议加上-a选项，并保障内存富裕；

6）为了方便后续维护可以适当加上批注，测试验证执行情况；

数据验证可以直接查看对应库相关表数据同步情况即可；

截止到此SSIS开发已完毕，剩下的是包生成部署以及配置成JOB的步骤了；

7).创建目录如下

注意

SQL Server Integration Services 服务是否已正常启动；

8).包生成部署

9).配置成可以定时执行的JOB

先要创建一个凭证

然后创建一个SSIS代理如下

配置数据同步JOB中step

在job中设置合适的计划等之后，测试验证

10)查看包的执行情况以及执行时长等信息；

也可以直接查看JOB的执行历史记录

结论

上述是一个简单的SSIS数据同步实践，不同的业务需求的业务逻辑都可在3)中自定义；
sqlcmd执行可能需要安装NET Framework 3.5, 否则sqlcmd无法正常执行；
sqlcmd 的前期测试，为了方便问题定位可以使用 -o 选项把错误信息打印出文本；
sqlcmd 后面建议加 -f 65001,避免中文在数据库中插入后，select原本是中文而显示乱码的情形；
确保部署的机器上SQL Server Integration Services 服务已安装并且正常启动；
确保SQL Server Agent 服务已正常启动；
在配置包的JOB的时候注意提前新建SSIS的代理，否则会有执行权限相关的问题；

当生成的执行脚本文件比较大的时候（比如超100M或更大），sqlcmd会报如下错误，建议在sqlcmd 加上 -a 选项，并保障执行sqlcmd的服务器有足够富裕的内存；

Shared Memory Provider: No process is on the other end of the pipe.
Communication link failure

---or 
Msg 701, Level 17, State 139, Server *******, Line -5057
资源池“default”没有足够的系统内存来运行此查询。

后记

待我完成本次测试，才突然想起为什么不直接使用SQL Server 自带的BCP来替换SQLCMD执行脚本。

exec master..xp_cmdshell '"F:Program FilesMicrosoft SQL ServerClient SDKODBC130ToolsBinncp.exe"  test.dbo.TCirOrder in F:TCirOrder.sql -E -n -C RAW -b 1000 -a 4096 -q -S butt-joint -T'

测试报如下错误

NULL
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
NULL
BCP copy in failed
NULL

根据这个指引把当前的SQL Server 2016 版本升到13.0.4224.16 还是如上报错,暂未找到合适的办法绕过去，处理过类似问题的朋友麻烦留言告知，谢谢；

--20190517 更新

后来发现直接在CMD中调BCP就没有上述问题，再写段POWERSHELL 批量执行BCP就可以。