October 14, 2009 at 3:05 pm
Hello,
I am trying to export data from a group of tables with different schemas (500 tables) to a matching set of tables in a different server. I am able to use SSIS OLEDB Source and destination to export data inside the data flow task for each individual table separetely (Very inneficient and long process). In the past, I tried using a control flow loop to look up the table names and loop through the table name variables to use the data flow data export, but the loop only works for tables that have the same schema or in other words, tables with the same definition otherwise, the loop breaks. I am just wondering if someone found a way to use a loop or something else to export data for a large set of tables with different schemas using SSIS. Doing it table by table is a tedious process.
Thanks,
Sean
October 14, 2009 at 3:08 pm
The only thing that comes to mind is building the package programaticually from .net and then executing it.. I haven't done this but it seems interesting..
CEWII
October 15, 2009 at 12:37 pm
October 15, 2009 at 1:29 pm
Now that is an interesting idea.. I think it could work..
CEWII
October 16, 2009 at 8:49 am
I tried it before using the Transfer Object task, but there are a lot of known issue whith that particular task in BIDS 2005. I am not sure if that was addressed in SQL Server 2008. When I tried it in the past, you couldn't transfer data without dropping and recreating objects, although it gives you the option not to drop destination objects ... (one of the issues). The option that I will exploring is to build this programatically. Thank you all for your help.
Sean
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply