Link to home
Start Free TrialLog in
Avatar of DrLechter
DrLechter

asked on

Copying large numbers of Oracle tables in SSIS

I need advice on copying a large number of tables from an Oracle DB into a MS SQL 2005 DB using SSIS.  

I noticed that SSIS seems to have a control flow Transfer SQL Object Task which allows copying any number of SQL tables in one shot including both schema (table design) and data.  

There doesn't seem to be an analogous task for copying from Oracle to SQL though.  It seems that I must define a separate SSIS data flow task for each table I want to copy.  Also, there doesn't seem to be an easy way to nuke the target tables and recopy the table design all in one shot.

Avatar of PedroCGD
PedroCGD
Flag of Portugal image

you can add a dataflow to your control flow for each target table you you can do all in same dataflow in paralell mode, but depend in the amount of data your are trying to transfer.

inside dataflow, you simple need to add an OLEDB Source and an OLEDB Destination, link both and mapp columns. OLEDB destination allows you to create the table in the target automatically (generate the CREATE TABLE statment and execute in target database.

If the schema is the same for tables, then you can do it dinamically and using only one OLEDB Source and Destination.

Regards,
Helped? If not let me know!
pedro

www.pedrocgd.blogspot.com
ASKER CERTIFIED SOLUTION
Avatar of Steve Hogg
Steve Hogg
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial