Import from an Excel file in SSIS 2012, numeric text field returns nulls RRS feed

  • Question

  • I have a simple package that is importing data from Excel, one column is numeric but has a leading '0' so is always 2 characters but 1 to 9 are '01' to '09', then the values are '10' to '60' but they are returning nulls, I tried the "IMEX=1" trick but this just makes that package return validation errors

    Any help would be much appreciated

    Andy May

    CRM 4, SQL Server and .Net developer using C#

    Friday, November 8, 2013 1:48 AM

All replies

  • Can you check the excel data flow component preview option ? Does the numeric column has the exact values or null.

    Regards, RSingh

    Friday, November 8, 2013 10:22 AM
  • Hi RSingh

    The preview looks the same as the imported data, where there is a leading zero the data is correct, which is the first few rows, then when the first character is non-zero the value is null



    CRM 4, SQL Server and .Net developer using C#

    Friday, November 8, 2013 7:44 PM
  • What is the version of the excel ? Can you try importing excel 97-2003 .xls  or try converting to other format like csv, tab delimited file.

    What is the datatype of the destination column ? If it is integer, change it into varchar or nvarchar and try once.

    Regards, RSingh

    Saturday, November 9, 2013 9:53 AM
  • Hi Andy,

    If you can use third-party solutions, check the commercial COZYROC SSIS+ library. It does include Excel adapters which doesn't exhibit you have described. In addition the adapters support both 32bit and 64bit execution.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Sunday, November 10, 2013 6:21 PM
  • Hi Andy,

    From your description, you encountered validation errors when adding “IMEX=1” to the Extended Properties of the Excel data source. It seems that you didn’t add this property correctly. Please use the following steps:

    1. In the Connection Managers pane, click the Excel Connection Manager.

    2. In the Properties pane, copy the ConnectionString, and modify it as follows:

    For .xls file, it is:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<Excel file path>;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1;"

    For .xlsx file, it is:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<Excel file path>;Extended Properties="EXCEL 12.0;HDR=YES;IMEX=1;"

    3. Copy and paste the above connection string to replace the current connection string.

    If the issue persists, you can try to modify the value of the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows registry to 0 and check the issue again.

    Mike Yin

    If you have any feedback on our support, please click here

    Mike Yin
    TechNet Community Support

    • Edited by Mike Yin Monday, November 18, 2013 2:42 PM
    Tuesday, November 12, 2013 9:39 AM
  • Should not it be IMEX=1????

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 12, 2013 10:38 AM
  • See:

    The first 8 rows are used to determine the datatype and in your case the excel provider will see 8 strings. All values larger than 9 are numbers and they will therefore be null.

    If you add IMEX=1 then it will switch the data type of mixed data type columns to string.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Tuesday, November 12, 2013 11:17 AM