Custom Replace: A Guide to Advanced String Manipulation in SSIS


This article will demonstrate examples of the Custom Replace option in our source components.


Use Case

When you want to replace a string in your source data, even to modify the structure.


  1. Drag and drop a Data Flow task into your SSIS package.
  2. Inside the Data Flow, add a Source component. In our case, an XML source.
  3. Select an XML source file or API; this is the XML we are using in our example:
 <Column1 type="Edm.DateTime">2023-02-06T00:00:00</Column1>
 <Column2 type="Edm.Decimal">4.00</Column2>
  1. Go to the Custom Replace tab and check Enable Custom Text Replace.

  2. Fill in the fields Search By and Replace By. In our example, we replaced “Edm” with “Abc”.

  3. In a second example using Regex, we remove part of the XML, leaving us with two columns. Use the expression in Search By: type="Edm.\w+"--regex, and leave Replace By blank.


This option will affect the entire source data.


Verify first if it does not affect the source file’s structure.


Following these steps, you can change the source data to simple text or a regex expression. If you encounter any issues or need further assistance, you can contact our support team via chat on our website or through email at