SSIS - importing 1000 tables

Question

Post reply

SSIS - importing 1000 tables

mazzz

SSCertifiable

Points: 6139
More actions
July 13, 2010 at 1:29 am

#221203

We're going to need to do a periodic import of data from about 1000 Oracle tables.
Am I going to have to create 1000 data flow tasks for this, or is there a less cumbersome way to approach this?
Any thoughts would be very much appreciated
------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ashwani Malik Ten Centuries Points: 1140 More actions · Answer 1

Ashwani Malik

Ten Centuries

Points: 1140

July 13, 2010 at 6:45 am

#1191979

Are all the tables in one database?

mazzz SSCertifiable Points: 6139 More actions · Answer 2

Yes. To clarify:

All source tables are in the same Oracle schema.

All destination tables will be in one SQL Server database.

------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]

Todasdasdfd asfasf Ten Centuries Points: 1340 More actions · Answer 3

Use the Import/Export Wizard to get your package started, then edit it in BIDS.

You won't necessarily have 1000 Data Flows, but you probably should. (You can put more than one distinct "flow" inside each Data Flow Task.)

Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.

mazzz SSCertifiable Points: 6139 More actions · Answer 4

Thanks Tod.

It's as I feared then - I will at any rate have 1000 distinct OLE DB Sources/Destinations to put together.

I counted the tables, there are actually 3000 of them!!

------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

mazzz (7/13/2010)
Thanks Tod.
It's as I feared then - I will at any rate have 1000 distinct OLE DB Sources/Destinations to put together.
I counted the tables, there are actually 3000 of them!!

Actually... not.

I'm not quite sure how to do it in 2k8 or SSIS but in 2000, we created a linked server to the Oracle server/schema. Then, we went into "Data Transformation Services" from Enterprise Manager, selected the linked server, selected all the tables we wanted and told it to copy the tables to SQL Server. We also told it to create any tables that weren't already created (I STRONGLY recommend you create a "staging" db to make cleanup a whole lot easier.

There's got to be a way to do the same thing in SSIS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Todasdasdfd asfasf Ten Centuries Points: 1340 More actions · Answer 6

There is - it's called the Import/Export Wizard...

Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.

mazzz SSCertifiable Points: 6139 More actions · Answer 7

Thank you for your responses, Jeff and Todd.

Even using the wizard to create the initial package I would still have to go in and manually edit about 2000 of the 3000 tables as there are some blob/clob columns we do not want to bring in. Then I'd be getting into the hell of referential integrity and insertion/deletion order...

The Oracle database is a 3rd party product so I have no knowledge of its metadata ( and no knowledge of Oracle to boot) , or I would have tried to script out the relevant actions to be performed nightly using a linked server maybe

My boss was hoping it would be a simple task for me to do, rather than paying for the 3rd party to do this, but it appears it would take up far too much of my time so paying the supplier would be cheaper!

Thanks again folks (and I'm not going to pretend I'm not relieved to offload this one!)

------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

mazzz (7/14/2010)
Thank you for your responses, Jeff and Todd.
Even using the wizard to create the initial package I would still have to go in and manually edit about 2000 of the 3000 tables as there are some blob/clob columns we do not want to bring in. Then I'd be getting into the hell of referential integrity and insertion/deletion order...
The Oracle database is a 3rd party product so I have no knowledge of its metadata ( and no knowledge of Oracle to boot) , or I would have tried to script out the relevant actions to be performed nightly using a linked server maybe
My boss was hoping it would be a simple task for me to do, rather than paying for the 3rd party to do this, but it appears it would take up far too much of my time so paying the supplier would be cheaper!
Thanks again folks (and I'm not going to pretend I'm not relieved to offload this one!)

3000 tables? Would it by any chance be SAP?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mazzz SSCertifiable Points: 6139 More actions · Answer 9

Jeff Moden (7/14/2010)
3000 tables? Would it by any chance be SAP?

No, it's a ticket/incident/change management application - Remedy.

------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]