CODEWITHSUNDEEP
sql-serverutf-8ssischaracter-encodingflat-file
Tsteenbakkers
Tsteenbakkers

Reputation: 294

SSIS - ANSI flatfile always saved as UTF-8 (w/o BOM)

I am facing an issue with SSIS where a customer wants a (previously delivered file in UTF-8) to be delivered in ANSI-1252. No big deal i thought. change the file connection manager and done... unfortunately it wasn't that simple. Been stuck on this for a day and clueless on what to try next.

the package itself

Data flow overview

IN - OLE DB source with a query. Source database fields are NVARCHAR.

Source Metadata

Next i have created a Data conversion block where i convert the incoming DT_WSTR to DT_STR using 1252 codepage.

Conversion to 1252

MetaData after conversion

After that is a outbound file connection destination. The flat file connection is tab delimited using codepage 1252. I have mapped the converted columns to the columns used in this flat file. Below are some screenshots of the connection manager and destination block

Destination block general

Destination block mapping

Flat file connection manager 1

Flat file connection manager 2

Flat file connection manager 3

Now when i create a new txt file from explorer it will be ANSI (as detected by Notepad++)

manually created file

When the package runs the file becomes UTF-8 w/o BOM

SSIS Output file

I have tried experimenting with the checkbox for overwriting as suggested in SSIS - Flat file always ANSI never UTF-8 encoded

as building the project from scratch and experimenting with the data conversion.

Does anyone have a suggestion on what I am missing here? The strange thing is we have a different package with exact the same blocks build previously and it does output an ANSI file (checked the package from top to bottom). However we are getting mixed results on different machines. Some machines will give an ANSI file other the UTF-8 file.

Upvotes: 2

Views: 6369

Answers (1)

Zsuzsa
Zsuzsa

Reputation: 427

Is this solved already? My idea is to delete the whole Data Flow Task and re-create it. I suppose the metadata is stuck and overwritten at each execution.

I believe you need not to change anything in your ssis package just check your editor setting (notepad++). Go to settings --> Preferences --> new document setting You need to uncheck the 'Apply to opened ANSI files' checkbox. Kindly check and let me know if it works for you.

Upvotes: 2

Related Questions