January 16, 2009 at 12:37 pm
I have a Oracle DB with 200 tables. Now my task is to make a direct dump of all the tables and also data into a sql db. how can I do this with SSIS. FOreach loop is my idea. Can you guys help me.
hope you understand. It is just a dump of tables.
THnaks in Advance.
January 17, 2009 at 5:38 pm
Create linked server in managment studio, then just make SSIS load data from ole db source (linked server tables) to ole db source in data flow.
January 18, 2009 at 10:15 pm
This might not be as easy as copying the data.
You could use a For Each loop to run an Execute SQL task that does something like:
Select * into SqlServerTable from OracleLinkedServer..User.Table
but that won't capture constraints or relationships between the tables. Say you copy a Customers table then later copy an Orders table. If this is a live transactional system you might have rows in the Orders table that were inserted after you finished copying Customers but before getting to Orders. This could mean the copy has orders for (new) customers that do not exist. Maybe your business requirement allows that, maybe not.
You might have already done this but I would want to see the DDL or a data model of the Oracle database so I can recreate it in its entirety. That will help reveal relationships and potential timing challenges in the ETL. Resolving them is a different issue.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply