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

Error Message:

Business Problem:

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.

Solution:

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.

Walkthrough:

1. Bring the Task Factory Replace Unwanted Characters Transform into your data flow and place it between your source and destination:
2. 
3. Next open up the Task Factory component and you will see a list of your columns on the left.
4. 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)
1. 
5. Select “Replace invalid characters that cannot be part of an XML Document.

Conclusion:

Now when I execute my package you will notice the illegal XML character is gone!

Thank you Task Factory Team!