asked
How to load data from multiple tables to multiple tables by using SSIS
I am using SQL Server 2008 R2. I have almost 106+ tables in database and now I need to load data from these tables into another database. Table structure of source and destination database are same. There is a status table in the database and all tables have a Primary Key ID field. All the tables have Primary key and Foreign key relationship. I need to transfer data those ID number will match with Status table where ID status is 1 or something like this. I need to insert Identity column also, and because of all the table have PK and FK relationship so i have to insert data into tables with referential constraints. So, my questions here is: To fulfill my above scenario how can I load data from multiple tables to multiple tables by using SSIS because it is not one time thing.
Is this a one time thing? Have you considered using Red Gate's Data compare tool? It can easily load all 106+ tables. You can get a 15 day trial version. I know that's not what you were looking for but I suspect it'd be a lot easier than trying to do this within SSIS. It's possible within SSIS just not fun to put together.
http://www.red-gate.com/products/sql-development/sql-data-compare/ Hope that helps!
18 People are following this question.
