Custom Replace: A Guide to Advanced String Manipulation in SSIS

SSIS General
1

Introduction

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

Prerequisites

Use Case

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

Steps

  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:
<data>
 <Column1 type="Edm.DateTime">2023-02-06T00:00:00</Column1>
 <Column2 type="Edm.Decimal">4.00</Column2>
</data>

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

    Custome Replace example 1
    Custome Replace example 1819×569 48.3 KB

  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.

    Custome Replace example 2
    Custome Replace example 2821×571 46.2 KB

Considerations

This option will affect the entire source data.

Tips

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

Conclusion

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 support@zappysys.com.