Excel Source component seems to be dropping data
-
I'm trying to read an Excel file using Excel Source component. In the file, I have certain columns that are mostly numeric, but certain cells in those columns are non-numeric.
For example, I may have a column that has
Column [F1]
100
200
ABC
300
400
Notice that the third row is non-numeric. When I try to read a colum like this, the behavior is 'ABC' is not coming across in the data flow. All the other cell contents come across as they are, but 'ABC' is coming across as NULL.
Also, if I try to do a SQL Statement like
SELECT
F1
FROM
[SheetName$]
WHERE F1 = 'ABC'
I get an error complaining about mis-matching data type. Now, when I look at the metadata on this column, SSIS has it as DT_R8 (double precision float). Why would it interprete this column as this type?
What do I need to make SSIS read this column as a string column so that I am not losing 'ABC'.
-
Check this message thread:
http://www.sqlservercentral.com/Forums/Topic423760-148-1.aspx
This is a known issue with Excel data provider.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Yes, After much research I did find this out, however, many of the articles that I read didn't really spell out how to implement this IMEX=1 property within SSIS.
I tried to look for it in the Excel Connection Manager, but it turned out I need to use an entirely different connection manager.
Bascially, I had to use .Net Providers for oledb\Microsoft Jet 4.0 OLE DB Provider connection manager and in it type in IMEX=1 into its Extended Properties.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply