In this fourth part of the series (see previous post here) we proceed to modify the XMl files downloaded from the web service. While this step is not mandatory in most cases where the XML is well formed, in this particular example we see an issue with the file. Namely
< And > text is represented as < and >
The file is in UTF 16 format instead of UTF 8 which will return an error when using XML Source stating byte order mask is not available in the file.
To correct these issue we simply need to parse the document and make the changes line by line. There are a number of tools available to achieve this I have gone with PowerShell.
| Step | Screenshot |
| Create a PowerShell Script to parse and replace strings within the file |
As you can see we iterate over each xml file in the path
Then read the contents of the file in the $i parameter. And search and replace the > to > and < to <
We then proceed to set the encoding for the file into UTF8
And save the file with ps1 extension to indicate it’s a PowerShell script.
With the Script ready we come back to the package and add an Execute process task to the package.
Configure the Execute Process Task as shown in the image
The Powershell Executable can be found in Windows-System32 Folder
The Arguments text box contains the path for the powershell script and the command
-ExecutionPolicy Bypass this is needed to allows Powershell to execute scripts, script execution is disabled by default in Powershell.
Lookup Set-executionpolicy for more info.
Press OK
With this we now have the files formatted as below
 |  |
Next we are ready to upload the file into our database table.
Please Consider Subscribing
