When Excel data is not coming into SSIS right, then do the following:
- use option “
IMEX=1” in ConnectionString (Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\..\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1") - modify registry setting “
TypeGuessRows=8” to a much higher value - use a sql statement to retrieve data (e.g. “
SELECT * FROM [sheet1$A5:M2000]“)
NOTE: The setting IMEX=1 tells the Excel driver to use Import mode. This mode reads the registry setting “ImportMixedTypes=Text” which forces mixed data to be converted to text. To achieve a more reliable column type recognition, the registry setting “TypeGuessRows=8” should be increased. By default the ISAM driver analyzes the first eight rows and determines the column datatypes from this sampling. If the first analysed rows contain only numeric values, then setting IMEX=1 will not convert this column datatype to Text – it will remain numeric.
ATTENTION: The IMEX setting has some other modes:
- 0 is Export mode
- 1 is Import mode
- 2 is Linked mode (full update capabilities)
Registry Setting Location:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\X.X\Engines\Excel
supplement
If you experience following error the above described solution could fix it.
1. An OLE DB error has occurred. Error code: 0x80040E21.
2. Failed to retrieve long data for column “XYZ”.
3. There was an error with output column “XYZ” (55488) on output “Excel Source Output” (109). The column status returned was: “DBSTATUS_UNAVAILABLE”.
4. The “output column “XYZ” (55488)” failed because error code 0xC0209071 occurred, and the error row disposition on “output column “XYZ” (55488)” specifies failure on error. An error occurred on the specified object of the specified component.
5. [DTS.Pipeline] Error: The PrimeOutput method on component “Info Source” (101) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
6. [DTS.Pipeline] Error: Thread “SourceThread0″ has exited with error code 0xC0047038.
Hi Jenny,
you should convert the column after the import to float data type and round it afterwards to 2 digits after the point.
I think this would be the best solution.
Best regards,
Ralf
Hi,
I have an issue when load a mixed excel data to table. The most values in the excel look like 2345.73 for some days while a few of them are 2345.74NT in another day so the destination column has to be varchar(). I tried to force the mixed data to Text but it then converted 2345.73 to 2345.729999999999999 which is painful as the data means an account number istead of a numeric amount.
I tried many ways such as conver function or data type converion in Derived columns such as from DT_R8 to DT_STR, no help.
The incorrect conversion (2345.73 to 2345.729999999999999) won’t happen only if the destination column is type of “Number” such as float or decimal. But it’s not acceptable as it means account number with some values like “2345.37NT”.
Very appreicated for any help.
Thanks,
Jenny