SSIS Flat File Connection - How does it determine string column DataType?

Question

I am creating a new Flat File Connection Manager SSIS component which is based on a CSV file. I am keen to have the columns (all 547 of them) to be of type Unicode string [DT_WSTR] rather than string [DT_STR]. I am not sure how to trigger this component to do this automatically. I guess I could go through and manually change each every one of the 547 columns to this data type of Unicode string [DT_WSTR] Any comments or answers much appreciated !

I have tried using the Unicode checkbox but the wizard then doesn't find the columns. I get message "The specified header or data row delimiter "{LF}" is not found after scanning 2097152 bytes .."

I was hoping there would be some way of changing all the column data types in one action without having to make 547 column type changes.

Answer 1

You can simply open the Flat File Connection Manager, Go To Advanced Tab, Click on one Column, Hold Ctrl key and select all columns then change the data type to DT_WSTR.

Additional information can be found in the following link:

• SSIS: Flat File default length

Answer 2

I found an answer to this question.

https://social.msdn.microsoft.com/Forums/en-US/747ad564-1add-422e-af3c-9375b130ec83/easy-way-to-set-all-data-types-in-a-connection-manager?forum=sqlintegrationservices

i.e. In the Flat File Connection Manager Editor it is possible to select multiple (or all) the columns and then the DataType choice made is applied to all the selected columns. Phew !

i.e. like this: