Post reply

SSIS - importing 1000 tables

  • 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]

  • July 13, 2010 at 6:45 am

    #1191979

    Are all the tables in one database?

  • July 13, 2010 at 6:52 am

    #1191986

    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]

  • July 13, 2010 at 8:26 am

    #1192059

    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.

  • July 13, 2010 at 9:12 am

    #1192122

    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]

  • July 13, 2010 at 9:22 pm

    #1192493

    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)
    Intro to Tally Tables and Functions

  • July 14, 2010 at 10:22 am

    #1192959

    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.

  • July 14, 2010 at 10:39 am

    #1192975

    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]

  • July 14, 2010 at 10:13 pm

    #1193230

    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)
    Intro to Tally Tables and Functions

  • July 15, 2010 at 12:17 am

    #1193258

    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]

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

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