SSIS issue trying to change Flat File Source Error Output Column

Question

I am having a problem trying to load data from a .CSV file to a SQL table using SSIS. In the Flat File Source, because my SQL columns in the table are data types nvarchar(max) I set all the DATA TYPES of my file source columns in the output columns to UNICODE text stream (DT_NTEXT) and after I did that I got the attached errors. When I tried to change the Flat File Source Error Output Column I got this error:

Error at Data Flow Task [Flat File Source [1155]]: The data type for "Flat File Source.Outputs[Flat File Source Error Output]" cannot be modified in the error "Flat File Source.Outputs[Flat File Source Error Output].Columns[Flat File Source Error Output Column]".

Error at Data Flow Task [Flat File Source [1155]]: Attempt to set the data type properties on "Flat File Source" failed with error code 0xC020400B. The error occurred while attempting to set one or more of the data type properties of the output column.

How do I fix this? Any help/direction would be appreciated.

Answer 1

Instead of editing the datatype in the flat file source component, edit them in the flat file connection manager. Go to the advanced tab, as shown below. Once you have modified this, it would be easiest to recreate the flat file source component so it can pick up the new metadata.