Geek asks and answers

How to prevent SSIS from deleting the last field of the last row of data in a flat file?

I have an SSIS package that decompresses and downloads a text file. It works great with the debugger and from the various servers that it uploaded on the way to our production environment.

My problem right now is this: the file is loading, everything is going fine, but suddenly, in the very last row of data (in accordance with the error message), the last field was truncated. I assumed that the file that we receive was probably corrupted, cracked, it was open, and everything is fine there.

A property with a delimiter, without a text delimiter, and as a line delimiter. Since the field with the truncation error is the last field in the line (and in this case, the last field of the entire file), its delimiter , not . | {CR}{LF} {CR}{LF} |

The file looks untouched, and I even uploaded it to Excel without any problems and no complaints. I ran this file through my local machine, running the package through the debugger in VS 2008, and it worked fine. Has anyone had any problems with this behavior? I can’t test it in the environment in which it crashed, because it is our production environment, and it’s rush hours ... so any advice is APPOINTED highly appreciated.

Error message:

Description: Data conversion failed. The data conversion for column "ACD_Flag" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". End Error Error: 2013-02-01 01:32:06.32 Code: 0xC020902A Source: Load ACD file into Table HDS Flat File 1 [9] Description: The "output column "ACD_Flag" (1040)" failed because truncation occurred, and the truncation row disposition on "output column "ACD_Flag" (1040)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. End Error Error: 2013-02-01 01:32:06.32 Code: 0xC0202092 Source: Load ACD file into Table [9] Description: An error occurred while processing file "MY FLAT FILE" on data row 737541.

737541 is the last line in the file.

: {CR}, {CR}{LF}, ... .

+5
5

, . , .

, , ... , , . : or one or more characters had no match in the target code page

Googleing , : http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/6d4eb033-2c45-47e4-9e29-f20214122dd3/

, , , , , 1252 ANSI Latin I 65001 UTF-8 .

, - , , 100% , , . , SSIS - Text_Qualified TRUE. false ( , ). , , , <none>, ... , .

, , . , , , , , , , , , .

: , : 10.50.1617 , ( , ): 10.50.4000. , , .

, , . - ( , , ), , . , , .

+4

:

, . , , .

, . , .

Sample file

.

Flat file general

Flat file Column 0

Flat file Column 1

Flat file advanced

Flat file Preview

, . , .

Failed

Execution Results BIDS. , .

[Flat File Source [44]] Error: Data conversion failed. The data conversion for column "Column 1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[Flat File Source [44]] Error: The "output column "Column 1" (128)" failed because truncation occurred, and the truncation row disposition on "output column "Column 1" (128)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[Flat File Source [44]] Error: An error occurred while processing file "C:\temp\FlatFile.txt" on data row 2.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Flat File Source" (44) returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

, .

Execution results

:

, , , OutputColumnWidth .

SSIS. Advanced Flat File Connection Manager Editor. OutputColumnWidth.

, - .

OutputColumnWidth

, :

  • OutputColumnWidth , .

  • , Flat. Flat File Source Editor, Error Output. Truncation Ignore failure Redirect row. Redirect row, , .

, .

Error output

+8

? ? . , .

xml. TextQualifier , :

 <DTS:Property DTS:Name="TextQualifier" xml:space="preserve">&lt;none&gt;</DTS:Property>

, .

+2

, , , "_x003C_none_x003E_". "_x003C_none_x003E_" , , "<" none " > " ( ), .

0

I had the same error. My source text file contained Unicode characters, and I solved it by saving the text file using Unicode encoding (instead of the default encoding utf-8) and selecting the Unicode check box in the Data Source dialog box.

0
source

More articles:


All Articles