The data type is DT_NTEXT, which is not supported with ANSI files

You will have to use the Data Conversion Component to convert the data if you cannot change your source columns.

Changing the source columns that is the easiest but not always an option. For example, I am returning a delimited list using XML FOR PATH and since I am doing this in a stored procedure, all I needed to do was convert the column to varchar(max) and the error went away and SSIS was happy.

select distinct 
    [WhatIfId],
      cast(stuff
        (
            (
                select 
                    '; ' + plr.[Label] 
                from [dbo].[track_rate_override_reasons_instance] tor
                join [dbo].[PickList_Loans_WhatIf_Rate_OverrideTypes] plt
                    on plt.[OverrideTypeId] = tor.[OverrideTypeId]
                join [dbo].[PickList_Loans_WhatIf_Rate_OverrideReasons] plr
                    on plr.[ReasonId] = tor.[ReasonId]
                FOR XML PATH ('')
            )
            , 1, 1, '') as varchar(max))  AS 'OverrideReasons'
from [dbo].[track_rate_override_reasons_instance]

Is it possible for you to wrap your source within a stored procedure so that you can manipulate the conversion?

For record, I faced similar issue today. Shuffling the data types between DT_TEXT, DT_NTEXT, DT_WSTR, DT_STR, nothing worked with both ANSI and UNICODE format combinations. Understood the issue was with source field that was of a data type varchar(max) - not sure why we assigned this data type. Change it to nvarchar(50) and the export worked without any issues and without any need to change the data type in export wizard.

For anyone looking for an alternative solution;

• Edit your Flat File Connection Manager
• In General tab
• Tick 'Unicode', OK

This solved the above problem for me.