We are trying to move to version 2 of our accounting data warehouse (more likely called a datamart), but it just doesn't have everything the department needs yet. So, we still have version 1 running, a system that gets updated just once each month instead of each night. The warehouses were designed by the accounting software vendor, so I haven't had much to do with the warehouses. I just need to get data from our production accounting system over to the warehouse staging database and do other routine DBA tasks. 

Version 1 was set up back in 2003. I was given a list of 43 tables and told where these tables needed to be copied.  The easiest thing to do was to use the CopyObject in DTS.  I also added an extra step to the job to compare the number of rows and aggretates of numeric and date data between the accounting tables and the copied staging tables to make sure the everything copied correctly.  That was fine for four years even though it took four hours to run each month.

We just rececntly upgraded our accounting system to a new version of the software and to 64 bit SQL Server 2005 (woo hoo!).  So, I figured all I had to do was point the CopyObject to the new server and all would be well. Or so I thought. I'm sure I could have found the solution if I had worked harder at it, but the CopyObject on the version 1 warehouse server would not talk to the new accounting database server. 

Since I had scripts in place on other servers to copy data around at night using linked servers, I decided to pop that solution into place instead of trying to figure out why the CopyObject would not work.  I am constantly coming up with new tables that must be copied around and this solution lets me do it by just adding the table name to a table called ImportTables.  As long as I have a linked server set up and there are select permissions on the source table, all works well. The new solution copied the 43 tables in just over an hour, 25% of the original time, and all the data checks were correct! To tell the truth, I was expecting worse performance not better. Could the improvement have been because the new source server was a much beefier box? I'm not sure.

When the original CopyObject DTS package was put into place, I hadn't written my linked server solution yet. In fact, I inherited dozens of DTS packages that only copied tables from one SQL Server to another, many copying just one table in a package.  I found that this was a nightmare to maintain, and I came up with the linked server idea.

I don't recommend that you scrap DTS or SSIS for linked servers. I'm just saying that you just need to experiment once in a while to see what will work best.