November 11, 2009 at 9:02 am
Hi,
I am trying to load Excel file into SQL table using SSIS Package. I included Data Flow Task for this with Excel File location as a source. There is 1 column in this excel file with which I am getting all trouble. For this column, First few rows are blank and there is no Datatype Format selected for this column in excel file, NO SELECTION.
When I put Data viewer between source and destination; I don't see any data populated for this column in Data Viewer!!!! It populates NULL for all rows in this column. If I specify data type as Number for this column in Excel file then it works fine. I don't want to specify this as that would be manual process and there is no scope for that.
There are other similar columns in this file for which Data Type is specified in excel file and i can see those columns very well populated with numbers into Data viewer. What should I do in order to get this field populated???
Any help would be great!!!
Thanks,
-RP
November 11, 2009 at 9:17 am
You need to set IMEX=1 in the connection properties. Google/Bing/whatever "IMEX" and you'll find the syntax for that, and some notes about what you may need to do in the registry to get it to work in certain circumstances.
It's pretty easy to set up, and it handles that exact problem.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2009 at 9:25 am
Thank you GSquared; That trick solved this issue right away. Thanks a lot!!! 🙂
November 11, 2009 at 9:27 am
You're welcome. Took me two weeks to figure it out the first time I ran into that. I take every chance I get to spare the pain for others.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply