locked
Oracle to SQL data importing and exporting RRS feed

  • Question

  •  Hi to all,

    I am importing data fro oracle to sql server by using import  export  wizard.

    1. in oracle i am exporting data to excel  file and importing that excel data  into sql server by using import  export  wizard.

    now my problem is i am getting below error while importing excel data into sql server.

    Pre-execute (Error)

    Messages
    Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37.An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E37  Description: "Invalid object name 'dbo.Sheet1$'.". (SQL Server Import and Export Wizard)

    Error 0xc0202040: Data Flow Task 1: Failed to open a fastload rowset for "[dbo].[Sheet1$]". Check that the object exists in the database.
     (SQL Server Import and Export Wizard)

    Error 0xc004701a: Data Flow Task 1: component "Destination - Sheet1$" (53) failed the pre-execute phase and returned error code 0xC0202040.
     (SQL Server Import and Export Wizard)

    * The SQL statement has been manually edited and the displayed values may not reflect the metadata of the destination table that will be created when executing the statement. 

    but i checked in my database there is no table like that name.

    please can any one have any idea about that.

    what i have to do for that ? please help me

    Thanks

     


    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Wednesday, March 7, 2012 6:07 PM

Answers

  • Can you check the destination table ([dbo].[Sheet1$]) defnition by clicking Edit SQL... button? Check the definition is in sync with the details you've provided in Mapping  grid shown in the screen capture. Pay attention to the table name, column names, and respective sizes... You can change the table name in 'Edit SQL' feature to  a meaningful name according to your context. And also try after selecting 'Drop and re-create destination table' even this is not required for the first time since table is not existing..

    - Krishnakumar S

    • Proposed as answer by Iric Wen Wednesday, March 14, 2012 8:10 AM
    • Marked as answer by Iric Wen Monday, March 19, 2012 8:54 AM
    Thursday, March 8, 2012 6:31 AM

All replies

  • Are you importing the Excel data to existing table (appending data) or creating a new destination table in your data base? What SQL statement you're edited? Is it 'Write a query to specify the data to transfer' or 'Edit SQL...' near to 'Create destination table' radio button. Looks like the Import/Export wizard is trying to import your Excel data to some table dbo.Sheet1$ (Default name for the Excel sheet if the name is not changed) that do not exists in database. What are the steps you've done for importing?

    - Krishnakumar S

    Wednesday, March 7, 2012 7:08 PM
  • Hi Krishnakumar,

    yes i am importing the Excel data into a new table not a existing table, by using import export wizard and  i am editing size.

     Thanks


    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Thursday, March 8, 2012 5:36 AM
  • Can you check the destination table ([dbo].[Sheet1$]) defnition by clicking Edit SQL... button? Check the definition is in sync with the details you've provided in Mapping  grid shown in the screen capture. Pay attention to the table name, column names, and respective sizes... You can change the table name in 'Edit SQL' feature to  a meaningful name according to your context. And also try after selecting 'Drop and re-create destination table' even this is not required for the first time since table is not existing..

    - Krishnakumar S

    • Proposed as answer by Iric Wen Wednesday, March 14, 2012 8:10 AM
    • Marked as answer by Iric Wen Monday, March 19, 2012 8:54 AM
    Thursday, March 8, 2012 6:31 AM
  • The error indicates the sheet name "Sheet1" does not exist in the source Excel file.  Please check your source Excel file.  Excel files are not a good method for export/importing tables.  I would suggest tab delimited or other delimited files.

    If you have access to both the Oracle and SQL Server, why are you exporting to Excel?  Why not just retrieve the data directly?


    Thursday, March 8, 2012 1:44 PM
  • Hi Tom,

    This is not related to Source Excel sheet name. I have recreated the same error meddage in my SSMS Import/Export wizard and this is due to mismatch in destination table name provided in 'Select tables and view' page and SQL script in 'Edit SQL...' dialog box. Looks like OP has made changes to default SQL generated.

    - Krishnakumar S

    Thursday, March 8, 2012 3:07 PM