none
Excel Import Error

    Question

  • Hello,

    I am trying out a simple SSIS package to import data from an xls into a table. For this I am using an Excel Source and an OLEDB Destination.
    One of the fields is called Notes. In the Excel Source, I have specified the Notes columns as Unicode string [DT_WSTR]. This maps to a SQL Table column which is nvarchar(max)
    The error messages that I get are :

    [Excel Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E21.

    [Excel Source [1]] Error: There was an error with output column "Notes" (272) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE".

    [Excel Source [1]] Error: The "output column "Notes" (272)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "Notes" (272)" specifies failure on error. An error occurred on the specified object of the specified component.

    Could anyone please help me with this.

    regards,

    Satya

    Thursday, August 24, 2006 9:23 PM

Answers

  • Hi Satya,

    I'm no expert so this is a best guess as to what might be happening. It sounds as though there may be rows in the excel file where the Notes filed contains data which isn't compatible with the DT_WSTR data type. What i would be inclined to do first is the following:

    Right click on the Excel data source object and bring up the advanced edit options. Select the Input and Output Properties tab and then the output columns folder under inputs and outputs treeview. You should (if i'm not mistaken) find a property for ErrorRowDisposition. Change this value to RD_IgnoreFailure.

    If you now save and run the package does it execute as expected. If there are indeed rows which have caused problems these should have been ignored by the transformation.

    Let me know if this helps.

    Cheers,

    Grant
    Friday, August 25, 2006 3:10 PM

All replies

  • Hi Satya,

    I'm no expert so this is a best guess as to what might be happening. It sounds as though there may be rows in the excel file where the Notes filed contains data which isn't compatible with the DT_WSTR data type. What i would be inclined to do first is the following:

    Right click on the Excel data source object and bring up the advanced edit options. Select the Input and Output Properties tab and then the output columns folder under inputs and outputs treeview. You should (if i'm not mistaken) find a property for ErrorRowDisposition. Change this value to RD_IgnoreFailure.

    If you now save and run the package does it execute as expected. If there are indeed rows which have caused problems these should have been ignored by the transformation.

    Let me know if this helps.

    Cheers,

    Grant
    Friday, August 25, 2006 3:10 PM
  • Great workaround, I had the similar problem, but is there any way to avoid the error itself?

    Thanks

    Atul

    Saturday, December 23, 2006 2:28 AM