Change encoding of XML file

• lukaszpiech

  SSCommitted

  Points: 1538

  More actions

  July 14, 2021 at 7:30 am

  #3907683

  Hello Everyone,

  While parsing XML files in SSIS i am getting an error “Invalid character in given encoding”.

  I can overcome this issue by manually replacing first line of code

  <?xml version="1.0"?>

  With below line of code

  <?xml version="1.0" encoding="ISO-8859-1" ?>

  Does any one of you know how to change encoding in SSIS package by replacing first line of XML document? Or maybe you know some other, better solutions?

  As an example we can use just basic XML document with UTF-8 encoding (file attached)

  <?xml version="1.0"?>
      <MyNode>
          <ChildNode>TEST</ChildNode>
      </MyNode>

  Can anyone help?

  Can i provide anything to help you helping me?

  Regards

  Attachments:

  You must be logged in to view attached files.

• Phil Parkin

  SSC Guru

  Points: 247169

  More actions

  July 14, 2021 at 7:41 am

  #3907685

  I know nothing about this specific problem.

  But adding a Script Task to edit and save the file before onward processing should be straightforward. Can you write a little C#?

  ---

• lukaszpiech

  SSCommitted

  Points: 1538

  More actions

  July 14, 2021 at 12:27 pm

  #3907780

  I can't unfortunately.

  So, script task is the way to go and C#.

  I see what i can do.

  Thank you!

• Phil Parkin

  SSC Guru

  Points: 247169

  More actions

  July 14, 2021 at 12:53 pm

  #3907813

  This could also be done in Posh.

  Have a go and see how you get on. Feel free to post back ... if you cannot get this working, I will put something together for you.

  ---

• lukaszpiech

  SSCommitted

  Points: 1538

  More actions

  July 23, 2021 at 9:50 am

  #3913121

  In the end i used powershell script like below to change Encoding to ASCII and that did the trick

   

  Get-ChildItem "PathToYourFolder" -Filter *.xml |
  
  ForEach-Object{
  
  $file=Get-Content $_.FullName
  
  $file | Set-Content -Encoding Ascii $_.FullName
  
  }