2

I am new to SSIS, I have already designed a package and configured SSIS Log provider for Text Files. This works fine and log files are generated successfully.

We have a monitoring team, they use this log file for monitoring. They are unable to read the log files since the file encoding is in Unicode format. They are expecting a non unicode format for their monitoring.

I tried to change the existing log file encoding to ANSI but when I re-run the package my log file has been created again with UNICODE encoding.

Is any way we can create log files using SSIS Log provider for Text Files with non unicode encoding. Kindly suggest me any workaround. I am unable to find solution for the past two days.

Hadi
  • 36,233
  • 13
  • 65
  • 124
StackUser
  • 5,370
  • 2
  • 24
  • 44

1 Answers1

1

Trying to figure out the issue

Since SSIS Log provider for Text Files use a File connection manager for logging purposes, you don't have the choice to edit the file encoding within the SSIS package because this type of connection manager can be used for different files format (excel, text ...).

While searching for this issue it looks like if the log is created for the first time by SSIS it will write unicode data.

Possible workaround

Try to create an empty text file using notepad and save it with ANSI encoding.

enter image description here

Then select this file from the SSIS logging configuration.

Other helpful links

Update 1 - Experiments

To test the workaround i provided i have run the following experiments:

  1. I add SSIS Logging and created and a new log file

enter image description here

  1. After executing the package the file is create in Unicode (to check that i opened the file using notepad and click Save As the encoding shown in the combobox is Unicode)
  2. I create a new file using Notepad and save it using Ansi encoding as mentioned above.
  3. In SSIS i changed the File connection manager to Use Existing instead of Create New and i selected the file i created

enter image description here

  1. After executing the package the log is filled within the file and the encoding is still Ansi
  2. I repeated executing the package several times and the undoing wont changes.

TL DR: Create a file with ANSI encoding outside the ssis package and within the package create a file connection manager, select Use Existing option and choose the created file. Use this file connection manager for logging purposes.

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • This works fine only for the first time. suppose If am again running the package the log file encoding changed to Unicode. – StackUser Mar 11 '19 at 12:10
  • @StackUser are you using `Create New file` or `Select Existing` option in the file connection manager?? – Yahfoufi Mar 11 '19 at 12:59
  • @Hadi This steps works fine for me but in my case the log file is appended with timestamp so every time the log file is creating newly. Hence this workaround not worked out for me. Thanks for your suggestion I will vote up for your efforts. – StackUser Mar 12 '19 at 12:48
  • @Yahfoufi I am using Create New File option since the log file is appended with timestamp – StackUser Mar 12 '19 at 12:51
  • @StackUser i think this answer gives you 99% from the answer since the 1% renaming is to add a script task to create the file on the package preExecute event and use an expression in the File connection manager to get the filename. note that you can use the `StartTime` system variable to get the timestamp. – Yahfoufi Mar 12 '19 at 13:12
  • @StackUser if the script task is generating file in Unicode, you can add an empty file (to be used as a template) with ANSI encoding, and add a `File System Task` that copy this file into a new one with timestamp each time the package is executed. – Yahfoufi Mar 12 '19 at 13:14
  • @StackUser i think what Yahfoufi mentioned is all what i was aiming to say. If you need any help to apply these suggestions, don't hesitate to ask – Hadi Mar 12 '19 at 19:33
  • Thank you for your suggestion Yahfoufi and Hadi, I have added one more step to SQL Server Agent job which convert my actual log file from Unicode to Ascii format using ssis c# script task. I have added this step to all my sql server agent jobs to do this conversion. I am not sure whether its a right approach but it works well. – StackUser Mar 26 '19 at 10:17