Weird chars at beginning of XML file

Question

Weird chars at beginning of XML file

Jay@Work

SSCrazy

Points: 2633
More actions
March 22, 2023 at 7:05 pm

#4163841
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?
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, 8 months ago by Jay@Work.
March 23, 2023 at 8:11 am

This was removed by the editor as SPAM

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

drew.allen SSC Guru Points: 76985 More actions · Answer 2

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

Jay@Work SSCrazy Points: 2633 More actions · Answer 3

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?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jay@Work SSCrazy Points: 2633 More actions · Answer 6

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);
                    }
                }

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

Simple and clear, quite impressive. Thanks for posting back.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin