SSIS – Starter kit for the uninitiated – Execute process task and PowerShell

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.

StepScreenshot
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.