ssis to refresh development data from production

Question

ssis to refresh development data from production

akl

Valued Member

Points: 67
More actions
August 26, 2011 at 9:07 am

#247082

when there is a database with hundreds of tables it gets very tedious to create individual data flow objects for each table. is there a way to use a for each loop with SMO objects to move source.database.table to target.database.table?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 245813 More actions · Answer 1

I assume you've decided against using BACKUP/RESTORE for some reason? That would be the simplest.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

akl Valued Member Points: 67 More actions · Answer 2

the restore to another server requires too many steps. back up prod, copy file across network, run restore, remove users due to login SID changes and restore users. I just want to move the data

Phil Parkin SSC Guru Points: 245813 More actions · Answer 3

akl (8/26/2011)
the restore to another server requires too many steps. back up prod, copy file across network, run restore, remove users due to login SID changes and restore users. I just want to move the data

I think you'll still find it's easier than handling lots of data deletes/recreates/truncates/transfers/whatever - but looking forward to being proved wrong.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

akl (8/26/2011)
when there is a database with hundreds of tables it gets very tedious to create individual data flow objects for each table. is there a way to use a for each loop with SMO objects to move source.database.table to target.database.table?

If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It is an extension of the standard Data Flow Task and supports dynamic data flows at runtime. You can accomplish the transfer of all the tables using one For Each Loop container and one Data Flow Task Plus. No programming required.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/