Recently I was using the Dimension Processing task inside of SSIS to perform a process add to one of my dimensions. I then found out that I had some bad data, more specifically, I had some invalid XML characters in my source data and this was causing the dimension processing task to fail. Below is a screenshot that includes the invalid character, after Mitchell before =.
I needed to remove invalid XML characters from source data so that I could use the dimension processing task to perform a process add on the dimension.
Sadly, I originally began this process by trying to write my own script transform to remove the invalid XML characters. Unfortunately the script I was using (Found on the internet) apparently did not account for all characters as my processing task continued to fail with the same error.
So what did I do? I turned to Task Factory of course! To solve this problem I used the Task Factory replace unwanted characters transform.
- Bring the Task Factory Replace Unwanted Characters Transform into your data flow and place it between your source and destination:
- Next open up the Task Factory component and you will see a list of your columns on the left.
- On the right you will see the column “Choose Action”. Click this drop down menu for the column you wish to cleanse. This menu will provide you with 4 different cleansing actions! (List below)
- Select “Replace invalid characters that cannot be part of an XML Document.
Now when I execute my package you will notice the illegal XML character is gone!
Thank you Task Factory Team!