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.

Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Steve Hogg

8/22/2022 - Mon

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.

Helped? If not let me know!

Steve Hogg

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck