Use the power of regular expressions to cleanse your data right there inside the Data Flow. This transformation includes a full user interface for simple configuration, as well as advanced features such as error output configuration.

Two regular expressions are used, a match expression and a replace expression. The transformation is designed around the named capture groups or match groups, and even supports multiple expressions. This allows for rich and complex expressions to be built, all through an easy to reuse transformation where a bespoke Script Component was previously the only alternative.

Some simple properties are available for each column selected –

Behaviour
The two behaviour modes offer similar functionality but with a difference. Replace, replaces tokens with the input, and Emit overwrites the whole string.

Cascade
Cascade allows you to define multiple expressions, each on a new line. The match expression will be processed into one operation per line, which are then processed in order at run-time. Multiple replace expressions can also be specified, again each on a new line. If there is no corresponding replace expression for a match expression line, then the last replace expression will be used instead. It is common to have multiple match expressions, but only a single replace expression.

Match Expression
The expression used to define the named capture groups. This is where you can analyse the data, and tag or name elements within it as found by the match expression.

Replace Expression
The replace determines the final output. It will reference the named groups from the match expression and assembles them into the final output.

If you want to use regular expressions to validate data then try the Regular Expression Transformation.

Quick Start Guide

Select a column. A new output column is created for each selected column; there is no option for in-place replacement of column values. One input column can be used to populate multiple output columns, just select the column again in the lower grid, using the Input Columns drop-down selector.

Amend the output column name and size as required. They default to the same as the input column selected.

Amend the behaviour as required, the default is Replace.

Amend the cascade option as required, the default is true.

Finally enter your match and replace regular expressions

Quick Sample #1

Parse an email address and extract the user and domain portions. Format as a web address passing the user portion as a URL parameter. This uses two match groups, user and host, which correspond to the text before the @ and after it respectively.

Behaviour is Emit, and cascade of false, we only have a single match expression.

Match Expression ^(?<user>[^@]+)@(?<host>.+)$

Replace Expression - http://www.${host}?user=${user}

Results

Sample Input	Sample Output
[email protected]	http://www.adventure-works.com?user=zheng0

The component is provided as an MSI file, however to complete the installation, you will have to add the transformation to the Visual Studio toolbox manually. Right-click the toolbox, and select Choose Items.... Select the SSIS Data Flow Items tab, and then check the RegexClean Transformation from the list.

Downloads

The RegexClean Transformation is available for both SQL Server 2005 and SQL Server 2008. Please choose the version to match your SQL Server version, or you can install both versions and use them side by side if you have both SQL Server 2005 and SQL Server 2008 installed.

RegexClean Transformation for SQL Server 2005

RegexClean Transformation for SQL Server 2008

Version History

SQL Server 2005

Version 1.0.0.105 - Public Release
(28 Jan 2008)

SQL Server 2005

Version 1.0.0.105 - Public Release
(28 Jan 2008)

Screenshot