Mixed type column import from Excel to SQL Server(IMEX cure)Posted: February 13, 2008
When you import data from excel to SQL Server, the mixed column will skip one type of data because of the use of TypeGuessRows in the background. By seeting the property IMEX to 1 in the connection string, this issue can be corrected. Here is the information about IMEX I grabbed from connectionstrings.com.:
"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative. Source: http://www.connectionstrings.com/?carrier=excel
The connection string I used for my SSIS package to loop through a folder to upload multiple excel files is:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::myFileName] + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES\";"
myFileName is a pakage variable in my SSIS pakage for looping(I tested this in a test SSIS pakage under "bioData1" at my office computer.