none
Oracle to SQL in SSIS - Large number of tables RRS feed

  • Question

  • Hi,

    I have setup a SSIS package that connects to a remote oracle database extracts the tables from the remote oracle database and dumps them in a local SQL Server instance. So far I have done 10 data flow tasks in SSIS for 10 tables and its taken me around 30 minutes to setup the 10 packages. I need to import around 1000 tables and I was wondering if there is anyway that I can setup a package that essentially selects everything from all the tables in a particular oracle instance, and dumps them into the local sql server so I don't have to create a data flow task for each one. 

    I seriously doubt that I will be able to do this, but there is no harm in asking. 

    Thanks

    Tuesday, May 17, 2011 1:17 AM

Answers

  • You might try using the Import Data wizard from the SSMS object explorer.  Right-click on the target SQL database and select Tasks-->Import Data.  After selecting the source tables and mappings, the wizard will create an SSIS package that you can save and/or run immediately.  I haven't personally used it to transfer data from Oracle so the might be some gotchas, especially with a large number of tables.  But it should at least do most of the heavy lifting for you.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by AshWood Tuesday, May 17, 2011 3:48 AM
    Tuesday, May 17, 2011 1:38 AM