How do I bulk-copy from SQL 2000 to SQL 2008, while updating schema changes on the fly?

We have a SQL 2000 database and a SQL 2008 data warehouse. The first must be copied each night to the second. Sometimes small changes are done on the first. We're trying to do a bulk copy that also copies the schema.

Before the 2008 upgrade, we used DTS for that. But now we're using Integration Services. IS seems to be unable to copy the schema like DTS can.

How to do this efficiently and flexibly?
Who is Participating?
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Well you could still use DTS, there is a download for 2008 for 2005 compatability which includes the DTS packages, so it does seem that MS have realised an ongoing need for DTS support. There is a new feature pack released in April :

Other wise, maybe a linked server. You can examine the structure of the table, and do data loads but will require some coding effort.

If the destination table is meant to be as per the source table, then you ccould also use the export wizard to create the "replacement" table...

Or there are a few third party tools as well...

If you could give an indication on a preferred approach then can give more explicit examples if needed.
All Courses

From novice to tech pro — start learning today.