Converting Nvarchar(max) source data to varchar(max) destination in SSIS

Question

I am trying to move the data from nvarchar(max) column in sql table to varchar(max) column of another sql table in SSIS package. It keeps giving me error at data conversion transformation of truncation.

I have two data conversion transformation:

1. one converts Unicode(DT_Ntext) to Unicode(DT_WSTR)(4000)
2. and second data conversion transformation converts Unicode(DT_Wstr) to String(dt_str) with 4000 length

Answer 1

In the OLEDB Source use an SQL command as access mode an cast column within the query:

Select CAST([column] as varchar(max)) FROM Table

When casting within SQL command there is no need to use Data Conversion Transformation in the Data Flow Task

In case that it is not a truncation error

If CAST() didn't works it means that the column contains some unicode characters which cannot be converted to a non unicode string, you can handle these values via the Error Output if you are using a Data Conversion Transformation or you can refer to one of the following links:

• Clean out all bad characters from a string
• Replace special characters in a column with space
• Remove non ASCII characters from column
• SQL Server: Function to remove Non ASCII Characters and Special Characters
• Remove Non Printable Characters from a String