I have two databases with approx 250 tables each. Database 1 is the staging DB and Database 2 is production. I need to append all the rows from staging to production.

In SQL Server 2000 I used a Copy Sql Server Objects task, checked all the boxes for the tables I wanted and told it to append the data. (yes I know this is not the most data/time efficient but it works like a champ and I only have one step that needs to be maintained )

I was recreating this step in SSIS and got an error stating that the connections to the two databases could not be on the same server.

Is this a bug/"new feature"?
Is it going to stay that way?

Any suggestions for copying/appending this data into production without 250 separate data transformation steps?

Suggestions welcome

Paul Pisarek