Microsoft CRM SSIS import failure due to truncations

Zoe Sands, 02 March 2016

While importing a CSV file into Microsoft CRM 2016 using a SSIS Integration Services Project, I encountered this error:

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

Error: An error occurred while processing file "Contacts Import.csv" on data row 92.

Initially I thought this was an issue with the size of the file. As I was importing over 4000 records. This turned out not to be the case as when the offending row was removed the import continued until it reached its next problem row.

Turns out each column has a maximum width in the Connection Manager. The import uses this metadata when it parses the CSV file. So when there is a field that exceeds its limit this error is thrown.

Follow these steps to increase the maximum width on any columns that throw this error.

1. Open the Connection Manager for the data source you need to edit

2. Go to Advanced Settings

3. Select the field that needs to be increased

4. Increase the field length

5. After you increase the field length anywhere you have used this connection will have to refresh its metadata the Source from your data flow will show up with a warning. You just need to open the element and the metadata will refresh