none
loading a CSV file with an SSIS Flat File Source Task

    Question

  • Hello,

    I am trying to load a csv file into a table using an SSIS flat file source task.

    However, I am getting an error because one of the the columns, definition, which is mainly string has numeric values included.

    I have set the property of the column (definition) as string [DT_STR] 8000.

    This is the error that I keep getting:

    [Flat File Source [242]] Error: Data conversion failed. The data conversion for column "Definition" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    I tried changing the datatype but it did not work.

    Does anyone know how to resolve this problem?

    Thanks,

    Kajoo

    Kajoo
    Thursday, March 04, 2010 12:14 AM

Answers

  • You'd have to
    1. Pre-process the file using a Script Task and .Net code, or
    2. Construct a custom Source component using a Script component and .Net code, or
    3. Use a better flat file source:
    Delimited File Reader Source - A source component capable of parsing delimited flat files, including files with rows that are missing column fields.
    Delimited File Source - An extremely robust flat file source for Integration Services which handles quoted text and provides full data auditing. Based on the work of Microsoft's Bob Bojanic (Delimited File Reader Source Sample).
    Todd McDermid's Blog
    • Marked as answer by Kajoo Tuesday, March 09, 2010 5:10 PM
    Monday, March 08, 2010 9:34 PM
    Moderator

All replies

  • The error that's being reported is telling you that you have some values in that file that exceed 8000 characters.  It has nothing to do with numeric values in that column.

    Have you tried a DT_TEXT data type for that column?

    Have you tried routing error rows from the source, and taken a look at which rows from your file are causing the error?
    Todd McDermid's Blog
    Thursday, March 04, 2010 12:52 AM
    Moderator
  • Hi Todd,


    I changed the data type to DT_TEXT but kept getting the same error.

    I also tried redirecting the data into a table but kept on getting this error:


    "Error: 0xC02020A1 at Data Flow Task, Flat File Source [242]: Data conversion failed. The data conversion for column "Definition" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    Error: 0xC020902A at Data Flow Task, Flat File Source [242]: The "output column "Definition" (275)" failed because truncation occurred, and the truncation row disposition on "output column "Definition" (275)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component."

    Thanks

    Kajoo


    Kajoo
    Thursday, March 04, 2010 1:13 AM
  • Are you expecting data in that column longer than 8000 characters?
    Todd McDermid's Blog
    Thursday, March 04, 2010 1:19 AM
    Moderator
  • It is really strange because now I am getting the following error for the "Notes" columns, which is the last column in the CSV comma delimited file.



    [Flat File Source [1]] Error: The column delimiter for column "Notes" was not found.


    Thanks
    Kajoo
    Friday, March 05, 2010 8:25 PM
  • It seems that your CSV file is not very well formatted.  The included Flat File Source doesn't do a stellar job of handling poorly formatted files.

    What you can do:
    - You can configure the Flat File Source to redirect errors to an error output, where you can then examine the rows in the file that are causing problems.  This may supply you with enough information to determine a root cause for the bad data, perhaps enough to allow the system that's supplying the file to be fixed.
    - You can use the Delimited File Reader Source or Delimited File Source instead.  They are much more tolerant of poorly formatted files.
    Todd McDermid's Blog
    Friday, March 05, 2010 10:34 PM
    Moderator
  • Hi Todd,

    Column notes appear to be OK. All the rows are wrapped with a quote; however, some of the rows have a carriage return within the quote so I keep on getting an error stating that

     

    “[Flat File Source [1]] Error: The column delimiter for column "Notes" was not found.”

     

     

    The problem is that there is a carriage return in some of the rows in the notes column.

     

    Is there an easy way to resolve remove the carriage returns in the quotes or resolve the CSV file?


    Kajoo
    Monday, March 08, 2010 8:42 PM
  • You'd have to
    1. Pre-process the file using a Script Task and .Net code, or
    2. Construct a custom Source component using a Script component and .Net code, or
    3. Use a better flat file source:
    Delimited File Reader Source - A source component capable of parsing delimited flat files, including files with rows that are missing column fields.
    Delimited File Source - An extremely robust flat file source for Integration Services which handles quoted text and provides full data auditing. Based on the work of Microsoft's Bob Bojanic (Delimited File Reader Source Sample).
    Todd McDermid's Blog
    • Marked as answer by Kajoo Tuesday, March 09, 2010 5:10 PM
    Monday, March 08, 2010 9:34 PM
    Moderator
  • I am currently using SSIS package 2005, therefore, I can't use the Delimited File Reader Source or Delimited File Source as they are built for SSIS 2008.

    So to remove the unwanted carriage returns in the notes column, I would have to use a Script Task and .Net Code. The problem is that I am not much of a coder. Is there a sample script that I can use to resolve the problem. 

    Thanks

     


    Kajoo
    Tuesday, March 09, 2010 5:31 PM
  • Start a new thread with this question - I don't know of any offhand.  I do remember John Welch's blog has some samples for script sources for flat files - but not for this particular case.
    Todd McDermid's Blog
    Tuesday, March 09, 2010 6:03 PM
    Moderator
  • I had the same thing happening.  What I did to fix it, is...

    Right-click on the Flat File Source and choose Advanced Editor from the submenu.

    Once in the Advanced Editor for your Flat File Source...

    On the "Input and Output Properties" tab

    Expand the "Flat File Source Output" in the "Inputs and outputs" window.

    Then, what you want to do, select each of your External Columns and compare the Length to the same column under Output Columns.  Make the Output Column length for each, match the External Column length.  (Make sure your DataType is the same too while you are at it.)

    When this is done and you get back to your Data Flow, if you have any downstream sorts, it might give you the metadata mismatch message -- when it asks you if you want it to automatically fix it, choose Yes.

    :-)

    Friday, February 18, 2011 8:22 PM
  • Also, if you find that you are still getting that error after doing the above, check your file values and see if there is any values in the field erring that is larger than the length you have defined.  If it is, you'll need to adjust both the Output Column length of the field and External Column length of the field to match.
    Friday, February 18, 2011 9:36 PM
  • More clear for above question is

    For example

    Actual output:  010218~0009~Z T WHOLESALE~                                                       A

    Expected output: 010218~0009~Z T WHOLESALE~A

    Open the Flat file connection manager and check the Column Type(Fixed Width | Delimited)

    If Column Type is Fixed Width output will be Actual output else  Expected output

    This property you can change using source code.

    Wednesday, June 22, 2011 10:09 AM