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

1
votes

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.

1
sql-serverutf-8ssischaracter-encodingflat-file
Is it possible the file already exists and it's using the existing encoding instead of recreating the file? Other than that your screenshots look like everything I would know to check... - lc.
try this, export your results to a raw file destination. On a second dataflow, import only the columns (from raw file source) which are in the in ANSI-1252 and save your flat file. My assumption that the utf-8 metadata in the pipeline might be crewing your file. - SQL.injection
I have tried several options: create an ANSI file and let SSIS append, create an ANSI file and let SSIS overwrite. Deleting the target file and let SSIS create it by itself. - Tsteenbakkers
And @SQL.injection: would there be an easier way than this? It seems like a legit option but it doesn't feel right. strangely enough when i convert the UTF-8 file to an ANSI file in notepad++ and save it upon re-opening the file is back to UTF-8 - Tsteenbakkers
@Tsteenbakkers the only thing I can think about is the metatada in the pipeline. by using a separate a separate dataflow you will make sure there is no UTF-8 on the pipeline. - SQL.injection

1 Answers

2
votes

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.