Export data from a select number of table in one sql database to another

• Schadenfreude-Mei

  SSChampion

  Points: 14083

  More actions

  November 3, 2010 at 9:14 am

  #228595

  Hi there,

  I'm quite new at ssis so please bare with me if i make any mistakes or miss the obvious.

  I have a database which is creating archive tables (old and obsolete data is moved from live tables into {name}_archive). The script that handles all that is by a 3rd party and im not allowed to touch it. My task is to copy all these _archive tables off to another database (for audit purposes) and then drop the tables from the prod database.

  I already have a script which populates a table with the names of the archive tables so all i need to do is export the tables to the other database based on the table names in my temp table..............

  And thats where i am stuck.At first i assumed i could use the 'Transfer SQL Server Object' task but it doesnt seem to allow you to feed it a table list based on a query. Can somebody please help me out.

  Maybe a script task?

  Thanks,

  Adam.

  Adam Zacks-------------------------------------------Be Nice, Or Leave

• Chrissy321

  SSCoach

  Points: 15763

  More actions

  November 3, 2010 at 3:45 pm

  #1246312

  See if these links help.

  http://www.help-sql.info/27/3/1120124.html

  http://consultingblogs.emc.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx