Monday, October 22, 2007

Excel Connection in SSIS

While trying to load Excel files, by default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. So, cases where the first 8-10 rows have empty data, the data type selected by SSIS may not be correct and we may face problems with uploading/transforming the data in these columns.

We can resolve this issue by adding "IMEX=1; MAXROWSTOSCAN=0" to the excel connection string extended properties. A typical excel connection string after adding the above properties will look as follows:


Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1; MAXROWSTOSCAN=0";

Labels: , , , ,

3 Comments:

Blogger Gigo said...

Want to convert data in xml file to sql db or string ...How to read ?? Pl Help

7:31 PM  
Anonymous Anonymous said...

I would appreciate more visual materials, to make your blog more attractive, but your writing style really compensates it. But there is always place for improvement

10:44 AM  
Anonymous Anonymous said...

Very shorts, simple and easy to understand, bet some more comments from your side would be great

3:43 AM  

Post a Comment

<< Home