Weird chars at beginning of XML file

  • Hi, each day we are sent an email with an XML attachment.

    Recently the XML file has acquired some strange characters prior to the XML tag (see screen shot).

    I have contacted the senders of the file and they claim nothing has changed at their end and that they do not see these chars prior to sending.

    As a result my SSIS package fails as the XML file is no longer recognized as a valid source.

    I have to edit the file to remove the chars and then run the package manually.

    1. Does anyone know what these chars mean/how they could have been inserted?

    2. Any idea on how to strip these out in the package prior to loading it as an XML source?

    xml

    I don't know if it helps but when pasted into plain old Notepad it is represented as ýýý<?xml

    • This topic was modified 1 year, 3 months ago by  Jay@Work.
  • This was removed by the editor as SPAM

  • I assume no one is suggesting that the act of sending this file is adding those characters?

    You should be able to prove that the file structure has changed by using a hex editor to open old and new versions of the file side by side.

    If you want to know whether it is possible in SSIS to 'edit' the file before loading, the answer is yes. A short script task will do that for you.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • You might also check the encoding of the old and new versions.  I know  that Notepad++ will show you the encoding.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • I can only take the senders word for it. They claim not to know how the files got these new characters and I'm assuming it is nothing at our end as I see the chars in the preview attachment view in Outlook.

    I added a script task to treat it as a text file, remove the 3 chars and re-save it.

    Was a good excuse to try OpenAI GPT-4.

    I asked for a c# script to do the job - an easy thing to do myself but I was curious.

    The output was spot on, needing no tweaks and it even provided advise on potential gotchas and assumptions that someone could add as code comments.

    Anyways long story, short is working fine now.

    But while I'm here. I added the script task, saved the file and deployed without issue.

    The job ran overnight and failed. Execution report said the script task hadn't been built (or something).

    I opened up the SSIS solution again on my client machine and got red squiggles under the using sqlserver.dts using statement and it wouldn't build.

    I closed and re-opened the script editor, the squiggles went away and I could build/save/deploy and the package ran perfectly.

    This isn't the first time I have seen this so wondered if it was something I missed or a bug?

    I have just upgraded from VS2019 to VS2022 so maybe it's been fixed?

  • Your post raises some interesting points.

    I can only take the senders word for it.

    I wouldn't, but your way leads to an easier life!

    Would you be prepared to share the code which was generated (sanitised, if needed)?

    I have just upgraded from VS2019 to VS2022 so maybe it's been fixed?

    Time for me to check this out. As usual, there was an age between the release of VS2022 and its SSIS extension.

     

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Time for me to check this out. As usual, there was an age between the release of VS2022 and its SSIS extension.

    Or maybe not! It's still in preview and looks like quite a few people are experiencing issues.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • It was nothing amazing (code wise) but just that it understood the exact requirements and seemed to spit it out on the fly

    public void Main()
    {

    // Define the file path and name of the original text file
    string originalFile = @"\\xxxxx";

    // Define the file path and name of the modified text file
    string modifiedFile = @"\\xxxxx";

    // Open the original text file for reading
    using (StreamReader reader = new StreamReader(originalFile))
    {
    // Read the contents of the file and remove the first three characters
    string fileContents = reader.ReadToEnd().Substring(3);

    // Open the modified text file for writing
    using (StreamWriter writer = new StreamWriter(modifiedFile))
    {
    // Write the modified text to the file
    writer.Write(fileContents);
    }
    }
  • Simple and clear, quite impressive. Thanks for posting back.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply