SSIS: How to transfer 170 tables of 3 gig from 1 database to another

• BEN-405167

  SSC-Addicted

  Points: 470

  More actions

  January 21, 2008 at 9:03 am

  #119694

  I got my 170 inputtables in 1 database, this is daily updated.

  They need to be transfered to tables in another database (on t he same server) containing a prefix imp_

  Do I need to creat 170 dataflow tasks or can I restore the inputdatabase and do a rename afterwards.

  Any suggestions

  Greetings,

  Ben

• russell-154600

  Ten Centuries

  Points: 1375

  More actions

  January 21, 2008 at 9:09 am

  #770174

  if u restore, can then execute following script in query window.

  change output to text, execute it, then copy paste back in and execute

  SELECT 'Exec sp_rename ''' + name + ''', ''imp_' + name + ''';' + char(10) + 'GO'

  FROM sys.tables

  WHERE is_ms_shipped = 0;

• BEN-405167

  SSC-Addicted

  Points: 470

  More actions

  January 21, 2008 at 9:33 am

  #770193

  Hello Russel,

  Thanks..

  Im not sure how to restore DB_X (170 tables) into an existing DB_Y.

  The restored DB_X tables need to be dropped and recreated every day

  Ben

• BEN-405167

  SSC-Addicted

  Points: 470

  More actions

  January 21, 2008 at 9:54 am

  #770202

  I think I can use the sql server objects task and then your renaming option.

  thx