SSIS error:
[Flat File Destination [112]] Error: Data conversion failed. The data conversion for column "LAST_FIRST" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
In this case the issue was bad character data in the db table...causing code page error.
Note - the character could also be good but just crashing SSIS due to flat file destination not having the correct code page
to support that character.
Steps to solve this error:
#1. On the OLE DB Source Editor - Error Output - Choose "Redirect Row" on the Column that is causing the failure
#2. Add a new Flat File Destination text file to store the redirected rows
#3. On the Data Flow Path Editor leading to the main output file - Data Viewers - Add a new OLE DB Source Output Data Viewer (Grid). Ensure the issue column is in the Displayed Columns list
At this point the SSIS package is now configured to re-direct any failed rows to a separate error log file.
Also enabled is a Debug Visual of the rows as they are loading (Data Viewer).
The issue with this package was only 4000 of the ~70000 rows were being sent to the main text output file. Checking this output file shows a partially loaded row at the end of the file. Querying the db showed a number of invalid characters in that row.
Remove the invalid characters or exclude that row from the results set to get the SSIS working again.
Note:
Another method:
Convert all the char or varchar fields in the SELECT list in your stored procedure to nvarchar()
Then change the SSIS Flat File Connection Manager Code Page to "65001 (UTF-8)".
Then ensure the SSIS Data Flow Path Editor..Metadata..Code Page = 0 (not 1252 which is ANSI))
This makes the db return Unicode data and the output file is also Unicode/UTF-8
(UTF-8 is a character encoding capable of encoding all possible characters, or code points, in Unicode. The encoding is variable-length and uses 8-bit code units)
Also, confirm your output file us indeed in UTF-8 encoding. Open the file in Notepad, click 'Save As...', in the 'Encoding:' box you will see the current file format.
Image 1:
Resources:
Debugging Data Flow
Add a Data Viewer to a Data Flow
Error Handling in Data
Configure an Error Output in a Data Flow Component
|
|
|
|
|
|