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

Question

Trying to export a table to a flat file by using Tasks / Export Data menu.

I am trying to export to ANSI 1252 Code Page.

My table has some nvarchar columns in it.

I am getting the message:

The data type is DT_NTEXT .... which is not supported with ANSI files ... Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component.

I have tried changing the source column mappings in the Columns Mapping section from Unicode string DT_WSTR to just string DT_STR, it didn't work.

Is it possible to export to ANSI file without altering my table? Is there an easier way? I couldn't find a way to change the source columns to regular varchar string.

Answer 1

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.

Answer 2

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?