Convert ODBC DB2 source to Flat File error in SSIS

Question

I have ODBC DB2 query to get data from database and data type of column only contain varchar(x), date, and int.

However, I'd like to keep them in flat file destination, but isn't able to use UTF-8 encoding. SSIS keeps informing error message below:

[Flat File Destination si_ce_f_hotel_capacity_snapshot_weekly [2]] Error: Data conversion failed. The data conversion for column "SOURCE_MARKET_CODE" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

This column has varchar(2) datatype from source. And I specify this datatype in ssis flat file structure as DT_WSTR (2).

However, when I change file format to Unicode, everything works just fine.

How do I get this work in UTF-8 ??

Thanks a lot for your answer.

Answer 1

You need to do a data convertion in the middle of your data flow. This can be done by using a Derived Column or Data Convertion Data Flow Transformation.

Answer 2

varchar -> DT_STR
Nvarchar -> DT_WST

You should specify the datatype in ssis flat file structure as DT_STR or use the ssis data conversion transformation tool