locked
Problem in importing data from Excel using SSIS in Sql server 2008 R2. RRS feed

  • Question

  • Hi All,

    I am trying to import data from Excel using SSIS and the problem is originating with the column containing numeric and text both types of data. As this type of data is not inserted properly into destination table only numeric data is inserting into it and all others are null values.I have tried all the data conversions inside the package but it is not working.

    Please provide me some help as i am stucked in this problem from a long time. 

    Thanks and Regards

    Thursday, June 12, 2014 12:56 PM

Answers

  • Hi Poornima,

    It’s a known issue with Excel Driver. The Excel driver reads a certain number of rows (by default, 8 rows) in the source to guess at the data type and length of each column. When a column contains mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. To avoid this issue, we only need to add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window. The connection string is like:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Excel\Template1.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1";

    Reference:
    http://msdn.microsoft.com/en-us/library/ms141683.aspx 

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Mike Yin Monday, June 23, 2014 5:02 PM
    Wednesday, June 18, 2014 9:33 AM

All replies

  • This is a common problem in Excel loading for SSIS, there is a setting for excel in registry called as TypeGuessRows , set it to 0. by default it will be 8. it means after 8 rows it does not consider alpha numeric data.

    Happy to help! Thanks. Regards and good Wishes, Deepak. http://deepaksqlmsbusinessintelligence.blogspot.com/

    Thursday, June 12, 2014 1:07 PM
  • Hi Poornima,

    It’s a known issue with Excel Driver. The Excel driver reads a certain number of rows (by default, 8 rows) in the source to guess at the data type and length of each column. When a column contains mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. To avoid this issue, we only need to add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window. The connection string is like:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Excel\Template1.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1";

    Reference:
    http://msdn.microsoft.com/en-us/library/ms141683.aspx 

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Mike Yin Monday, June 23, 2014 5:02 PM
    Wednesday, June 18, 2014 9:33 AM