Post reply

SSIS,Fixed width File,Derived Column Transformation,Error:Conversion for column 0 returned status value 4

  • October 15, 2011 at 9:24 am

    #250088

    Hi

    I have a fixed width flat file having 1300 positions and 165 columns.

    I am trying to import this to a SQL server table.

    I have a FlatFileConnection and a derived clolumn transformation where I give the derived columns using the SubString function giving the columns start positinos and length.

    I wanted to know when I am assuming the complete 1 line as 1 column in the flat file and using the derived -column transformation to get all the columns:

    1>In the Flat File connection manager, what shall I pick as Format:Delimited, Fixed Width or Ragged

    2>How do I know what is the Header Row Delimiter? By default it shows {CR}{LF}

    3>Why the connection manager not asking the row delimiter? Why just the header row? What if I dont have the header row in the file?

    4>I tried selecting the Format as Ragged & then Delimited, in both cases it shows me the correct 1 Column and rows in the Preview

    but when I am runnnig the package, the package fails in the very first step (Flat File Connection Manager) The error is-->

    Data conversion failed, data conversion for column 0 returned status value 4 . Text was truncated or 1 or more characters had no match in the target code page.

    I am confused, why this error in the Flat File Connection manager step?? Its not even coming to the Derived Column Transformation step to throw the error.

    Any idea why this error or if I am doing anything wrong? Somebody suggested me to manually hit <Enter> after each row...this is not possible as there are more then 6000 rows.

    What is the exact method of using a derived-column transformation and substrings to get all the columns from the fixed width file?

    Any ideas/solutions ? Thanks in advance!!!

  • October 15, 2011 at 11:14 am

    #1394976

    SJanki (10/15/2011)

    Hi

    I have a fixed width flat file having 1300 positions and 165 columns.........

    Data conversion failed, data conversion for column 0 returned status value 4 . Text was truncated or 1 or more characters had no match in the target code page.</quote]

    Hi All

    Actually I had to set 'TruncationRowDisposition' to 'RD_IgnoreFailure' and it worked then, no truncation error.

    But I still have to find the reason for truncation (error)

    Thanks!!

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

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