locked
Importing data from approx 400 Oracle tables RRS feed

  • Question

  • I am working with SSIS to import approx. 400 tables from an Oracle database.  Each table to be imported, is stored in a table.  I created a for loop to retrieve each of these tables.  As part of this looping, I create and store a select statement in a variable.  Note, each table contains different number and name of columns.  Next, I created a data flow task.  This data flow task has a OLE DB Source control and a OLE DB Destination control.  In the source control task, I set the data access mode to SQL command from variable.  Variable name contains a Select statement.  In the OLE DB destination control, I use data access method "tablename or view name variable - fast loaed".  When I try to execute my package, i am getting a error indicating that a column cannot be found at the datasource.  Further investigation indicates that my source columns are not refreshed at runtime as a result of changing them from design time.  

    Can I change the column definition at run time?  If so, how?

    I would appriciate your help.
    Monday, December 15, 2008 2:24 PM

Answers

  • No, you can't change the column metadata at run-time.  To transfer 400 tables, you would need 400 data flow components.  You might want to check out the Import/Export wizard from SSMS. i am not sure if this will work with Oracle, but, it is worth a try.
    Dave Frommer - RDA Corporation
    • Marked as answer by Tony Tang_YJ Friday, December 26, 2008 9:09 AM
    Monday, December 15, 2008 4:19 PM

All replies

  • No, you can't change the column metadata at run-time.  To transfer 400 tables, you would need 400 data flow components.  You might want to check out the Import/Export wizard from SSMS. i am not sure if this will work with Oracle, but, it is worth a try.
    Dave Frommer - RDA Corporation
    • Marked as answer by Tony Tang_YJ Friday, December 26, 2008 9:09 AM
    Monday, December 15, 2008 4:19 PM
  • Thanks,

    I was afraid that was true.
    Monday, December 15, 2008 4:27 PM
  • or you need to do that by customizing your package using script component or even custom component...
    But it's hard and depending on the source schema...

    Visit http://www.pedrocgd.blogspot.com - If your question is answered, please mark as answered.
    Monday, December 15, 2008 6:32 PM