-3

I am loading a cleaned CSV file into SSIS using a Flat File Source, but the Data Flow keeps failing with the following error:

Data conversion failed. The data conversion for column "LOC_STREET_TYPE" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."

The full error message also includes:

The PrimeOutput method on Flat File Source returned error code 0xC0202092. Text was truncated or one or more characters had no match in the target code page.

What I have done so far:

  1. Exported the CSV using Python with CRLF row endings and encoding latin1 / ANSI (code page 1252)

  2. Set the Flat File Connection Manager to 1252 (ANSI – Latin I)

  3. In the Advanced tab, I increased the OutputColumnWidth for LOC_STREET_TYPE from the default to 100

  4. Opened the Flat File Source “Advanced Editor” and also updated the output column length for the same column

  5. Clicked “Yes” when SSIS asked to sync metadata

  6. Re-ran the package, but the error persists at different rows (line 136, line 137, etc.)

The column LOC_STREET_TYPE contains short string values such as: ST, AV, BLVD, WAY, PKWY, DR

Nothing exceeds even 10 characters, so truncation should not happen.
However SSIS still reports truncation or code page mismatch.

enter image description here

enter image description here

enter image description here

Improve this question
New contributor
HUYỀN NGUYỄN THỊ MỸ is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2
  • Sounds like the Flat File Source is getting out of sync. Does your data contain any comma or newline characters by chance? Did you use the Python csv module and a csv_writer to generate your CSV files? Did you use quoting=csv.QUOTE_ALL or at least QUOTE_MINIMAL? Commented yesterday
  • FYI, giving your images a description other than "enter image description here" is really helpful when your images have no context on what they are showing. Commented yesterday
Related questions
89
Errors in SQL Server while importing CSV file despite varchar(MAX) being used for each column
0
Importing CSV in SSIS: truncation error
1165
Writing a pandas DataFrame to CSV file
Load 5 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.