-1

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.

Jim
  • 10,765
  • 17
  • 74
  • 114
  • I could not agree with you more about the downside of DTS packages with complex, lengthy scripts. It's even more difficult in SSIS to click around all those little task boxes to see snippets of the code. I had to support a 57-step overnight DTS job once, and migrating that to SSIS was gonna be awful – DOK Nov 14 '08 at 14:42

3 Answers3

2

you can use BCP to export and then import data.

Mladen Prajdic
  • 15,018
  • 2
  • 39
  • 48
0

Use Red Gate Sql Compare and Data Compare?

edosoft
  • 16,270
  • 24
  • 75
  • 108
0

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

wcm
  • 8,625
  • 6
  • 35
  • 63