none
Import Access table or Excel spreadsheet into Oracle table

    Question

  • I am trying to import from an Excel spreadsheet or an Access database into an Oracle table.  I used the SSIS Import and Export wizard to create the SSIS packages, but everytime that I attempt to run the package, SSIS stops once it gets to the Pre-Execute phase.  There are never any errors.  Am I doing something that cannot be done?

    I'm using the following:  SSIS 2005, Microsoft Oracle OLEDB provider (MSDAORA.1), Excel and Access 2003

    By the way, I am able to successfully export from the Oracle database to either Excel or Access using SSIS.


    Troy
    Monday, September 27, 2010 5:31 PM

Answers

  • We were able to determine the problem.  We were using the Microsoft ODBC driver for Oracle.  Once we switched to use the Oracle ODBC driver, our problem was resolved.

    We determined this after we removed the Oracle destination task from the data flow, and then added it back, but with a different driver.


    Troy
    • Marked as answer by tfruete Thursday, October 07, 2010 5:25 PM
    Thursday, October 07, 2010 5:25 PM

All replies

  • Something is locking up the Excel spreadsheet I think. You may want to restart the machine then try again, also please set "DelayValidation" in your DFT then retry, too.


    Arthur My Blog
    By: TwitterButtons.com
    Monday, September 27, 2010 5:58 PM
  • check this example http://plexussql.blogspot.com/2010/04/looping-through-excel-files-and-sheets.html

    PLEASE NOTE: the final destination is a SQL table you have to change it to Oracle.

    and for access you must make a second package see http://www.mssqltips.com/tip.asp?tip=1437


    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Monday, September 27, 2010 6:31 PM
  • I have tried rebooting the machine and have set the DelayValidation in the DFT to TRUE, but it has not helped.  When I run the package, the DFT box turns yellow on the Control Flow tab, but no boxes turn yellow or green on the Data Flow task.
    Troy
    Monday, September 27, 2010 7:22 PM
  • The package must be still executing if it is yellow. How large is the file?
    Arthur My Blog
    By: TwitterButtons.com
    Monday, September 27, 2010 7:24 PM
  • The Excel spreadsheet has one row with headers and one row with data.  There are 5 columns.  Of these 5 columns, only a couple have data, and the rest are null. 
    Troy
    Monday, September 27, 2010 8:29 PM
  • Were you able to preview the row? If yes.

    At this stage I would create a new package, or delete all the tasks and re-create this functionality.


    Arthur My Blog
    By: TwitterButtons.com
    Monday, September 27, 2010 8:32 PM
  • I do have two different SSIS packages.  One for Excel and one for Access

    My package is not complex.  All that I want to do is connect to a known Excel spreadsheet (or Access database) in a known location, and import it's contents into an Oracle database.  The examples that I reviewed are really not that different from what I am already doing, except I am not using any scripting, or looping.  I used the SSIS Import Export wizard to create the initial package. 


    Troy
    Monday, September 27, 2010 8:38 PM
  • Another option to validate that all is good is to use DTSWizard.exe (run form the Run command) and try exporting the data from Excel to Oracle this way, see if that works and let us know (shut BIDS down beforehand).
    Arthur My Blog
    By: TwitterButtons.com
    Monday, September 27, 2010 8:43 PM
  • I am able to preview the row.  I have recreated the package several times.  I've tried deleting everything and building it from scratch, but I keep getting the same result.  I only have a few tasks.  (All of which were built by the wizard):  On the first tab, all that I have is the Data Flow Task.  On the second tab, all that I have is the following:  Source - Data Flow Component, Data Conversion (which is used to conver the Excel data to the proper code page in Oracle), and a Destination - Data Flow Component.
    Troy
    Monday, September 27, 2010 8:46 PM
  • Hi Troy,

    The issue is a little odd. In order to troubleshoot the issue, we need to isolate the problem, could you please try following the steps as below:

    1. Remove the Oracle destination from the Data Flow Task(DFT), and then check if the package can be run. If so, the issue may be caused by the Oracle destination.
    2. Remove the Data conversion or the Destination, and then check if the package can be run. 
    3. If it still fail. The issue may be caused by the Excel file. Please try to create a new Excel file, and change the source to point to the new file. After that, check the excecution again.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Tuesday, September 28, 2010 7:23 AM
  • I removed the Oracle destination from the DFT.  The package ran successfully.  I then re-added my Oracle Destination, and I had to set the AlwaysUseDefaultCodePage property = TRUE.  However, It's back to the same old thing, where it will not enter any of the boxes on the Data Flow tab.
    Troy
    Tuesday, September 28, 2010 3:36 PM
  • One more thing that I have tried:  The first box on the Control Flow tab is an Execute SQL Task.  This task simply updates a table in the Oracle database.  That task is successful 100% of the time.  I have tried my package with the task included, and with it excluded, with no impact.
    Troy
    Tuesday, September 28, 2010 3:42 PM
  • just like to point out , because you are using a Excel file you must use the 32bit DTExec.exec file in the future to run the package.

    One question , in your connection string are you using the IMEX = 1 ???? (this is for the excel file only)


    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Tuesday, September 28, 2010 6:07 PM
  • We were able to determine the problem.  We were using the Microsoft ODBC driver for Oracle.  Once we switched to use the Oracle ODBC driver, our problem was resolved.

    We determined this after we removed the Oracle destination task from the data flow, and then added it back, but with a different driver.


    Troy
    • Marked as answer by tfruete Thursday, October 07, 2010 5:25 PM
    Thursday, October 07, 2010 5:25 PM