none
Some problem in Data Flow while upload a "||" seperated text file

    Question

  • I am facing one problem with sql 2005 SSIS while uploading a "||" seperated text file with Data Flow in SSIS.Below I am giving the sample data of the text file

    data||data||data        

    data||data||data

    When I uploaded it using sql 2000 DTS it was not giving and it was being uploaded into the table but when I tried with SQL 2005 it is giving the below problem.

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

     

     

    Tuesday, July 25, 2006 12:34 PM

All replies

  • It sounds as though you have defined the length of [Column 2] to be too short. You probably need to widen it. If so, this is nothing to do with the '||' edlimiter.

     

    -Jamie

     

    Tuesday, July 25, 2006 12:40 PM
    Moderator
  •  

    Hi Jaydip,

     

    Is this pipe character or special character.

    If its special character, it might be code page 285 where DB2 database hosted on AS/400.

     

    Can you please let me know if you got the answer, cuz I am alos facing the same problem.

     

    Regards

    Makarand

    Monday, October 08, 2007 10:23 AM
  • Hi ,

      I too facing this same issue. My source file is delimited by TAB.
    The column width for column2 is 60 and i have cross checked several times that i have created the column width as 60 in SQL Server table for the corresponding field. Please help me to resolve this as soon as possible. Thanking in advance
    Friday, December 14, 2007 9:58 AM
  • Check the field definition in the flat file connection manager, and make sure none of the data in column 2 exceeds the 60 character limit. Also, make sure the file does not contain any non-standard characters.

     

    Saturday, December 15, 2007 4:11 AM
    Moderator
  • The flat file is generated from Mainframes so there it has been limited to store only 60 characters. But i am not sure about the non-standard characters. Since the flat file contains more than 500 records How can i check?
    Saturday, December 15, 2007 10:27 AM
  • Could you redirect rows with truncations to the error output and save them into another file? That should help to find such rows.

     

    Thanks,

    Bob

    Monday, December 17, 2007 6:56 AM
    Owner
  • [Source - LGT-QA_txt [1]] Error: Data conversion failed. The data conversion for column "Column 27" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    [Source - LGT-QA_txt [1]] Error: The "output column "Column 27" (118)" failed because truncation occurred, and the truncation row disposition on "output column "Column 27" (118)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

    [Source - LGT-QA_txt [1]] Error: An error occurred while processing file "C:\Documents and Settings\.......\Desktop\LGT-QA.txt" on data row 899.

    By looking at the above error description, i copied the content of the file and paste it in to an excel sheet. But i am unable to find the reason why it is not loaded. I checked the last column (Column 27) and found few spaces in it. That column is declared as character in SQL. What should i do now?
    Wednesday, December 19, 2007 1:01 PM
  • Did you follow Bob's advice and redirect erroring rows to another location?

     

    -Jamie

     

     

    Wednesday, December 19, 2007 2:41 PM
    Moderator
  •  on7june wrote:
    The flat file is generated from Mainframes so there it has been limited to store only 60 characters. But i am not sure about the non-standard characters. Since the flat file contains more than 500 records How can i check?


    You missed the point.  Check the Flat File Source component for columns that are less than 60 bytes (causing truncation).  Right click on the flat file source component and select properties.  From there, select "Advanced Properties."  Then, look at the external columns (coming from the flat file itself) and the output columns (columns that are going into the data flow).

    Also, ensure that in the flat file connection manager object, that you haven't specified columns that are too large for the incoming data.

    One other thing you can do is double click on the green line coming out of the Flat File Source component and view its metadata.  Do the columns show 60 byte lengths?
    Wednesday, December 19, 2007 2:46 PM
    Moderator
  • thanks, this answer helped me a lot.
    Tuesday, October 12, 2010 3:38 PM