I am trying to load Excel 2007 files into SQL server. I am using Access ACE connection and openrowset to open the sheet. Some columns are mixed type of numbers and chars and SSIS/ACE driver will treat the data type as numeric even though the character starts beyond row 8. All character value is loaded as NULL.
This issue is asked and answered by many people already. I have researched all possible threads online. I have tried various combination of these
connection string: IMEX=1 vs IMEX=0
used both regedit and regedt32 to launch registry editor.(seems no difference)
Hard coded connection string vs package level variable.
Registry GuessTypeRows = 0-16
None of them works. I am using 64 bit machine, windows 7 and BIDS 2008. Excel is 2007. 64 bit debugging mode of BIDS is disabled. I am using ACE provider that connect to Excel file.
Here is my connection string in the expression. Just an FYI, it works well.
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::vExcel_Filename]+";Extended Properties=\"Excel 12.0 XML;HDR=YES;IMEX=1;\""
I was able to trace down to the bottom of my problem, which maybe different from other people had same issue. The registry change does not take effect. No matter what value I put in GuessTypeRows, no matter it's 0, or 16 or decimal/hex. I can not get the data type to character if the first character value is beyond row 8. (i.e. char value in row 8 will work regardless what setting I put in the registry).
Any idea why the registry value not working? Do you have any suggestion how I can further test it?
I don't think the data type you specify in the input/output columns matter here. In fact, I have both columns showing unicode string 255 char long. I think it's the "excel connection" that uses Jet engine detect the datatype on the fly. In my case, it only scan first 8 rows.
I have researched for two days now and tried all kind of possible solution that is reported working by other people. I am very disappointed.