SSIS中动态Transfer SQL Server Objects Task性能问题求助

问题背景 #

你用SSIS的Transfer SQL Server Objects Task GUI配置迁移200张表，耗时约30分钟；但因为要频繁更新迁移表清单，改用C#脚本任务动态创建Microsoft.SqlServer.Management.Smo.Transfer对象，循环添加表后，性能直接翻倍，扩展事件日志也没找到原因，纠结要不要回到手动管理清单的老路。

已知情况：性能差异是社区常见问题 #

不少用户都遇到过这个情况，核心原因在于SSIS原生Transfer任务和手动写的Smo.Transfer脚本底层逻辑不一样：

• SSIS GUI配置的任务是批量处理所有表，底层复用了数据库连接、批量加载的优化逻辑，元数据读取也是一次性完成；
• 你现在的脚本是循环逐个添加表，相当于每次添加都重复触发部分初始化操作，额外开销累积起来就拖慢了整体速度；
• 另外，Smo.Transfer的默认配置和SSIS任务的默认配置可能不一致，比如是否开启批量复制、批量大小设置等，这也会影响性能。

优化脚本任务的具体方法 #

1. 批量添加表，避免循环逐个操作 #

不要每次循环都调用Transfer.ObjectList.Add()，先把所有要迁移的表对象收集到集合里，一次性添加到Transfer的对象列表中，减少重复初始化的开销：

// 示例代码：批量收集并添加表
List<Table> targetTables = new List<Table>();
Server sourceServer = new Server(new ServerConnection(sourceConnStr));
Database sourceDb = sourceServer.Databases[sourceDbName];

// 从配置列表读取表名，批量获取表对象
foreach (string tableName in yourTableList)
{
    targetTables.Add(sourceDb.Tables[tableName]);
}

// 一次性添加到Transfer对象
Transfer transfer = new Transfer(sourceDb);
transfer.ObjectList.AddRange(targetTables.ToArray());

2. 调整Smo.Transfer的性能相关配置 #

把脚本的配置和SSIS GUI任务的配置对齐，重点开启批量复制、调整批量大小：

// 启用批量复制API，和SSIS任务逻辑对齐
transfer.UseBulkCopyApi = true;
// 设置合适的批量大小，根据数据量调整，比如10000
transfer.BulkCopyBatchSize = 10000;
// 保持和GUI一致的删除目标对象、复制Schema的配置
transfer.DropDestinationObjectsFirst = true;
transfer.CopySchema = true;
// 关闭不必要的进度日志，减少IO开销
transfer.LogProgress = false;

3. 复用数据库连接对象 #

不要在循环里重复创建Server和Database对象，初始化一次即可：

// 只初始化一次源和目标服务器/数据库
Server sourceServer = new Server(new ServerConnection(sourceConnStr));
Database sourceDb = sourceServer.Databases[sourceDbName];
Server destServer = new Server(new ServerConnection(destConnStr));
Database destDb = destServer.Databases[destDbName];

// 基于同一个连接创建Transfer对象
Transfer transfer = new Transfer(sourceDb);
transfer.DestinationDatabase = destDb.Name;
transfer.DestinationServer = destServer.Name;

4. 替代方案：Foreach循环+Data Flow任务 #

如果不想折腾Smo脚本，也可以用SSIS原生组件实现动态迁移：

• 把要迁移的表清单存在数据库表、JSON文件或者配置表中；
• 用Foreach循环容器读取这个清单，每次循环把表名赋值给变量；
• 在循环内部放一个Data Flow任务，用OLE DB源和目标，通过表达式动态设置表名，配置为“截断目标表后加载数据”；
• 这种方式的性能和原生Transfer任务接近，而且完全支持动态表清单，不需要手动修改包。

总结 #

完全不用回到手动管理表清单的模式，不管是优化Smo脚本的配置，还是改用Foreach+Data Flow的方案，都能解决性能问题。Smo.Transfer和SSIS原生Transfer任务的性能差异是已知的，调整配置后就能大幅缩小差距。

内容的提问来源于stack exchange，提问作者SKing