2

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.

Community
  • 1
  • 1
Tsteenbakkers
  • 264
  • 3
  • 18
  • 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. Feb 20 '15 at 09:20
  • 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 Feb 20 '15 at 09:25
  • 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 Feb 20 '15 at 09:26
  • 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 Feb 20 '15 at 09:29
  • @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 Feb 20 '15 at 09:32
  • @Tsteenbakkers you can always use a script component to write the file to the disk. A bit like on the programing classes at school – SQL.injection Feb 20 '15 at 09:33
  • It seems like the idea of writing to a RAW file does not work. The RAW file is in ANSI format but when using that file again in a new DF the output flat file is still UTF. I will try the script task in a bit. could it be something else like a mistake by Notepad++ or a system dependancy? – Tsteenbakkers Feb 20 '15 at 11:15
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/71335/discussion-between-tsteenbakkers-and-sql-injection). – Tsteenbakkers Feb 20 '15 at 14:09

1 Answers1

2

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.

Ankita Maru
  • 3
  • 1
Zsuzsa
  • 417
  • 5
  • 14
  • Hi Ankita, Thank you for your contribution. I have already re-created the complete package from scratch but this gave no solution... However.... The suggestion to edit preferences in notepad ++ did however change the 'problem'. Apparantly this 'New Document' feature also applies to opened documents. Case closed! – Tsteenbakkers Feb 24 '15 at 11:12