SSIS Export OLEDB Source to a Flat File with UTF-8

Question

I am trying to export an OLEDB source (from a stored procedure) to an UTF-8 flat file, but am getting the following error:

[Flat File Destination [2]]

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

The name column is defined in the stored procedure as nvarchar(30).

In the advanced editor of the OLEDB source, I have the AlwaysUseDefaultCodePage set to true and the DefaultCodePage set to 65001.

In the advanced editor for the flat file for the External Columns and Input columns, the Data Type is Unicode string [DT-WSTR] with a length of 30.

The connection manager for the flat file has the Unicode checkbox un-checked and the Code page is: 65001 (UTF-8).

I am stumped right now and any help would be appreciated.

Thanks, David

EDIT:

I added a redirect of errors and truncations to a flat file destination but nothing was sent to the file.

Also, when I have a data viewer on the OLE DB source it comes up with all the records.

The data viewer for the Destination also shows all the records. The length of the name in both viewers is 30 characters (from Excel).

Answer 1

I gave up on getting the data flow to work and coded a C# script task instead.

I changed the output of my data flow to produce a Unicode file by checking the Unicode check box in the flat file connection manager.

I then have the new C# script read the Unicode file one line at a time and output the line to another flat file using Encoding.UTF8 adding a new line character at the end of the line variable.

After the new file is created, I delete the input file and rename the new file to be the same path and name as the original input file. This is also done in the C# script.