April 23, 2010 at 2:38 am
Hi All,
I have a excel file in which there si a column 'Phone id'. in 1st 100 rows the values for this column is numeric value and after that it have test value like 'XPS100'. so when i'm uploading this into sql server 'XPS100' value is uploaded as null. my destination table column has varchar data type for this column and excel sheet has genrel format for this column.
so i think after seeing 1st 100 row its taking data type as int and after that when it gets text value its ingoring that.
Can you guys have any suggestion for this problem.
Thanks
Sneh
April 23, 2010 at 2:55 am
This is a very, very, very common problem with SSIS and Excel. It shows up in hundreds of blog and forum posts.
Have you tried Google?
The issue is that Excel guesses the datatype based on a fixed number of rows of that column. If it sees x times a number, the column will get a numeric datatype, thus rendering NULL for your string value later on.
How to fix this? Set IMEX=1 in your connection string. This will tell the driver that if there are intermixed datatypes, to convert everything to text. Furthermore, you have to set a registry value to 0, telling the driver to scan the whole column, and not just the first x rows.
Reading material:
http://support.microsoft.com/kb/189897 (registry key)
http://swap.wordpress.com/2007/09/18/solution-for-excel-data-uploading-problem-using-ssis/ (IMEX=1).
I think I will copy this post and paste it somewhere on my computer. Then I can re-use it for the next time someone asks this question. 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 23, 2010 at 4:52 am
Thanks for your reply.
but i'm getting error "potential lose of data while excuting the pkg". what i do:- 1st give IMEX=1 in connection string this time i got the same result. again i also do the regedit property as 0 and i'm getting the error. even i got error when the pkg start.
April 23, 2010 at 5:57 am
That's because the datatypes of the output columns of your Excel source are probably still the same. (meaning, still DT_I4, while you need DT_WSTR). Either change it manually in the advanced editor, or just delete the Excel source and add a new one.
SSIS doesn't refresh metadata when you change something. Just to annoy you 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 23, 2010 at 9:24 am
Because of these, and other issues with Excel as the source I make it a practise to run all columns from Excel through a Data Conversion Transformation and manually configure each data type. Tedious, but it works pretty consistently.
April 26, 2010 at 4:30 am
Super wow!!!!
Raunak J
April 26, 2010 at 4:30 am
Super wow!!!!
Raunak J
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply