Post reply

Loading from mixed data type in XLS file

  • January 21, 2010 at 9:14 am

    #138396

    Hi,

    I am building a ssis package that loads data from XLS file that has mixed data type columns.

    I have applied the IMEX=1 in the ConnectionString that is loading all data correctly for one column but not the other column. The other column also has mixed data more numeric and nulls and some text and in the 'Show Advanced Editor' window this column is automatically set as double-precision float [DT_R8] and is only loading numeric and null values and is loading null values for text values.

    Can anyone help please?

  • June 10, 2010 at 10:52 am

    #1178765

    If you can use third-party solutions, check the commercial CozyRoc Excel components. These are the relevant components:

    * Excel Source component - for reading data from Excel worksheet.

    * Excel Destination component - for writing data in Excel worksheet.

    * Excel Task - for manipulating Excel workbooks.

    * Excel Connection - used by the components above and also for implementing custom scripts based on it.

    The CozyRoc Excel components doesn't try to guess the format of the column, so you will get much more predictable behavior, compared to the default Excel components.

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

  • June 11, 2010 at 7:11 am

    #1179143

    I don't know if this registry hack works with SSIS or not - I haven't tried it:

    http://searchwinit.techtarget.com/tip/0,289483,sid1_gci1059475,00.html

  • June 11, 2010 at 7:14 am

    #1179149

    Ed-86789 (6/11/2010)

    I don't know if this registry hack works with SSIS or not - I haven't tried it:

    http://searchwinit.techtarget.com/tip/0,289483,sid1_gci1059475,00.html

    The registry hack works. It should be best practice to set the value of that registry entry to 0 on all machines. Otherwise the Excel Source can give unexpected results.

    Conclusion: change the registry setting and the problem should be solved. And you don't even need 3rd party solutions for that.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply