none
.csv flat file import to Sql Server 2005 via SSIS: Data type conversion errors

    Question

  • Tried searching but didn't find a thread that covered this exact problem.

    Attempting to import a comma-delimited .csv file from network directory location into a SQL 2005 data base.  I'm using a staging table, scripted from the destination table, then moving the data on to the destination table.  The specific data in the columns in question is one or two digit integers.

    The column datatype in the staging and destination tables is "int".  I'm given to understand that all columns from a .csv file would be treated initially as datatype "string"?

     

    In any case, this is the error I'm getting:

    [Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Qualified Pegs" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

    In the "flat file source" advanced editor I've tried changing the datatype to various "int" datatypes: DT_I8, DT_I4, etc.

    It's only throwing the error for this column now, but I suspect that once it gets fixed it will throw a similar error for all the numerical datatype conversions.

    There are about 39 columns per row and potentially hundreds of thousands of rows per import, so I'd rather avoid using a Data Conversion step.

    Any help?


    "I am a Soldier. I fight where I'm told and I win where I fight" GEN George S. Patton, Jr.
    Tuesday, December 14, 2010 9:41 PM

Answers

  • seems that you have inappropriate values in the fields in csv file, try to configure error output on flat file source , and redirect errors to another flat file destination, in this way you can catch bad values which caused error.

    http://www.rad.pasfu.com
    • Marked as answer by tanksoldier Wednesday, December 15, 2010 7:20 AM
    Tuesday, December 14, 2010 10:31 PM

All replies

  • seems that you have inappropriate values in the fields in csv file, try to configure error output on flat file source , and redirect errors to another flat file destination, in this way you can catch bad values which caused error.

    http://www.rad.pasfu.com
    • Marked as answer by tanksoldier Wednesday, December 15, 2010 7:20 AM
    Tuesday, December 14, 2010 10:31 PM
  • I agree with Reza and to add to it, you could also add a dataviewer to see the error in a grid output. I would only recommend to do this for debugging purposes. Seeing it in the grid veiwer may help you to quickly identify the records causing the error. 
    Please 'Mark as Answer' if found helpful - Chris@tier-1-support

    Tier 1 Support
    Wednesday, December 15, 2010 2:17 AM
  • Yep.  Found the problem.  The file is from an external source.  It is comma-delimited, but for a name column they chose to use a: "Smith, John" format.  So SSIS is breaking the name into two columns, because of the comma: /"Smith/ John"/, and bumping everything past that to the right.

    Thanks.

    "I am a Soldier. I fight where I'm told and I win where I fight" GEN George S. Patton, Jr.
    Wednesday, December 15, 2010 7:20 AM