在服务器之间传输数据的方法有很多种.您可以将链接服务器与 OPENROWSET 查询一起使用，您可以使用 BCP 实用程序和临时文件，或者您可以使用 PowerShell 脚本仅举几例.

最合适的方法取决于您的具体情况，但对我而言，SSIS 通常是最合适的技术，尤其是在涉及转换的情况下.SSIS 保持着 ETL 世界纪录(尽管这是对大量硬件的人为实验)，因此应该能够满足您的性能需求.

您可以做很多事情来提高 SSIS 数据移动的性能，但您的问题中没有足够的信息来确定您需要做什么，但这里有一些关于 SSIS 性能的提示:>

• 确保数据流中的缓冲区大小设置适当，通常高于 10000 行默认值，但确切大小取决于您的内存容量.

• 确保您使用的是快速加载的 OLEDB 目标

• 下载平衡数据分发器组件并并行加载表.这还需要在堆或按顺序的数据上使用 TABLOCK，在聚集索引上使用 TF 610.

• 避免同步转换，例如 Sort.

We have many internal SQL Servers running, and there are some daily jobs transfering data. and we also have some external database, we'll use view or txt to tranfer data.

now, we are using DTS & SSIS. but because of some reasones, our manager do not like DTS/SSIS, so we are moving out from DTS/SSIS, but we still need to tranfer data. they suggest to develop some application, for load data/file, and then insert into the destination.

is this a good solution? any suggestion?

I think the only reasonable reason is: if there's a performance issue, it's hard to find the reason in DTS/SSIS

Thanks very much!

There are many methods of transferring data between servers. You could use a linked server with a OPENROWSET query, you could use the BCP utility, with a staging file, or you could use a PowerShell script to name just a few.

The most appropriate depends on your exact circumstances but for me, SSIS is often the most appropriate technique, especially if transformations are involved. SSIS holds the ETL world record (although this was a contrived experiment with massive hardware) so should defiantly meet your performance needs.

There are many things that you can do to improve the performance of SSIS data movements, and there is not enough information in your question to determin exactly what you need to do, but here are a few pointers for SSIS performance:

• Make sure the buffer size in the data flow is set appropriately, normally higher than the 10000 rows default, although the exact size depends on your memory capacity.

• Make sure you are using OLEDB Destination with Fast Load

• Download the Balanced Data Distributor component and parallel load the tables. This will also require TABLOCK on a Heap or per-ordered data and TF 610 on a Clustered Index.

• Avoid synchronous transformations such as Sort.