I was having this crazy problem with SSIS today.. Basically I was trying to read out a excel file, which has multiple data types in the same column.. (The problem is neatly described by this blogger :http://microsoft-ssis.blogspot.com/2011/06/mixed-data-types-in-excel-column.html )
Summary of the problem: Excel always tries to "determine" the data type of the column. And in SSIS (As of 2008 R2), there is no way to override it. (Microsoft says that this is Excel's limitation). So, If you add "IMEX=1" in the extended properties, it solves "most" of the issue.
But wait.. If the first 8 rows have numeric data and subsequent rows have string, then this approach fails. So as a workaround, you should modify this registry key : HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows (For 64 bit, HKLM\SOFTWARE\wow6432node\microsoft\jet\4.0\engines\excel)and change it up to 16.
Another workaround suggested by MS - (http://support.microsoft.com/kb/281517) Add the rows which has string on top, you will just be fine !!!
Summary of the problem: Excel always tries to "determine" the data type of the column. And in SSIS (As of 2008 R2), there is no way to override it. (Microsoft says that this is Excel's limitation). So, If you add "IMEX=1" in the extended properties, it solves "most" of the issue.
But wait.. If the first 8 rows have numeric data and subsequent rows have string, then this approach fails. So as a workaround, you should modify this registry key : HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows (For 64 bit, HKLM\SOFTWARE\wow6432node\microsoft\jet\4.0\engines\excel)and change it up to 16.
Another workaround suggested by MS - (http://support.microsoft.com/kb/281517) Add the rows which has string on top, you will just be fine !!!
No comments:
Post a Comment