An excellent alternative is the SSIS Import/Export Wizard: http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/ It allows you to edit field types. Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 Grand Slam

An excellent alternative is the SSIS Import/Export Wizard: http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/ It allows you to edit field types. Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 Grand Slam

HI Please find data below ColA ColB 74378 11213 312 21312 34 234 7329 27924 23423 224353 42342 13243 54245 32321 25423 6483 5234 84379 dmf 934293 52 9549 49 439879 ads asdf asd aesdf asd asdf asd asdf asdf asdf sadf asdf asdf sadf Using excel connection to load data to SQL Server When I try doing this character data is loading as nulls, I have tried by putting IMEX=1 property in connection string and removing IMEX=1 property from connection string , in both cases it is showing nulls, in the data viewer which i have placed after Excel source. I have also tried using the OPENROWSET which for some reason does not create an adhoc connection. I also tried changing the Datatypes in the advanced editor which again throws error. I tried all possible ways of fixing this issue but no luck yet. Could you please help resolve this. For some reason SSIS excel connection reads only the first 8 rows and decides a datatype and rest showup NULL.

set the IMEX=1 in excel connection manager's connection string property like this: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\..\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1http://www.rad.pasfu.com

Hi Thanks for your response. I tried doing both with and without IMEX=1 that did not help either.

could you paste your connection string here exactly? also you have another option: you can go in this address in registry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\X.X\Engines\Excel and change the TypeGuessRows to the higher value than 8 rows, this will by default scan only first 8 rows to find data type of columns. also you can copy one or two rows with character data to top of excel file and re-create excel source .http://www.rad.pasfu.com

Hi Thanks for your reply. Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\\FileShare\SharedDocs\Test.xlsx; Extended Properties="EXCEL 12.0;HDR=YES;IMEX=1";

connection string seems correct. did you tried my other suggestions?http://www.rad.pasfu.com

Hi, does not allow me to change values in the registry.

so try to copy one or two of rows with character data in first 8 rows beside rows with integer data, and re-create excel sourcehttp://www.rad.pasfu.com

The problem with your data is that Excel is attempting to guess the data type of the columns. By default, the TypeGuessRows property is set to 8, which means it will sample the first eight rows. In your data, the first eight rows are all numbers, so Excel interprets the entire column as numeric. When it encounters text, it decides it can't convert text to numbers, so it provides SSIS with a NULL. IMEX has no effect in the above scenario (with the default TypeGuessRows value) because IMEX applies when Excel finds "mixed types" in the columns - and in your case (with eight rows) it does not. TypeGuessRows can be modified (as Reza suggested) but only for values from 0 to 16. In your specific case, setting it to 16 would have an effect. Excel would read your file, see some numeric AND text values, and decide those columns have "mixed types". NOW the IMEX attribute comes into play. If you DON'T specify IMEX=1, then Excel will choose a data type based on majority wins. In your specific case, it would still choose numeric for column A and column B, because most of the values in the first 16 rows are numeric. If you specify IMEX=1, then Excel behaves differently. Instead of "majority wins", if multiple data types are detected, it ALWAYS picks text. So - in your specific case, with this specific file, increasing TypeGuessRows to 13 or more, AND using IMEX=1 will allow you to read both columns as text, and not receive NULLs. Talk to me now on