Move data accross SQL Servers with DTS packages or linked servers

Question

We are using SQL Server 2000 and have to move data across servers to store it in a central database. We would rather not use DTS packages because we will move to SQL Server 2005 soon, and we don't want to have to upgrade to SSIS or have to support legacy DTS packages.

Is there any way to use OSQL scripts, vbscript, or other command line tools to do this?

DTS packages would work, but I'm a little hesitant. They get ugly quickly. This job will have to combine data from a total of 200 tables accross 18 databases on 3 different servers. I don't want to have to drag and drop hundreds of data transformation tasks into a DTS package GUI. I'd rather have everything listed plainly in an batch file or set of scripts.

Answer 1

you can use BCP to export and then import data.

Answer 2

Use Red Gate Sql Compare and Data Compare?

Answer 3

Depending on the complexity of the DTS package, the SSIS upgrade wizard isn't that bad.