Post reply

Problem loading Excel files: Mixed types changed to NULL, even with IMEX=1

  • July 7, 2011 at 1:37 pm

    #241134

    Hi,

    I am struggling with loading Excel files in SSIS. If a colums contains mixed alpha and numeric characters it gets changed to null. Here is my connection string:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CDP\Paiq\Public\1_A_Traiter\interfaces\Chargement_INOC_IVANHOE_2010T4.xlsx;Extended Properties="EXCEL 12.0;HDR=YES;IMEX=1";

    What is odd is that when I run only the data flow task, it seems to work fine (as I can see in the data viewer). But when I run the whole package, then the problem with the nulls happens.

    Any help would be much appreciated.

    Chris

  • July 7, 2011 at 1:46 pm

    #1349993

    there's afew posts out there that describe how the driver scans the first 8 rows to determine the data type;usually you can set the extended property "IMEX" of provider to value of 1. This property tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text.

    however, i think i read that if it doesn't show as alphanumeric in the first 8 rows(or 100?) or that the data is < 255 chars, even with that setting, you can get the error you are experiencing. I seem to remember that you might need to fiddle with the data to guarantee that at least one row in the first 8 rows contain data length greater than 255, (if it was truncating data) or contains alphanumeric characters.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 11, 2011 at 1:20 am

    #1350904

    The registry setting Lowell mentioned is the TypeGuessRow registry setting (for the JET provider).

    There is a similar one for the ACE provider.

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

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

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