Regular Expression Transformation

The regular expression transformation exposes the power of regular expression matching within the pipeline. One or more columns can be selected, and for each column an individual expression can be applied. The way multiple columns are handled can be set on the options page. The AND option means all columns must match, whilst the OR option means only one column has to match. If rows pass their tests then rows are passed down the successful match output. Rows that fail are directed down the alternate output.

This transformation is ideal for validating data through the use of regular expressions. You can enter any expression you like, or select a pre-configured expression within the editor. You can expand the list of pre-configured expressions yourself. These are stored in a Xml file, %ProgramFiles(x86)%\Microsoft SQL Server\nnn\DTS\PipelineComponents\RegExTransform.xml, where nnn represents the folder version, 90 for 2005, 100 for 2008, 110 for 2012, 120 for 2014, 130 for 2016, 140 for vNext

If you want to use regular expressions to manipulate data, rather than just validating it, try the RegexClean Transformation.

Downloads

Please choose the version to match your SQL Server version. You can install multiple versions and use them side by side if you are developing for multiple versions of SQL Server. For the recent releases of SQL Server Data Tools that can target different versions of SQL Server, please install all target server versions your intend to use.

Regular Expression Transformation for SQL Server 2005

Regular Expression Transformation for SQL Server 2008

Regular Expression Transformation for SQL Server 2012

Regular Expression Transformation for SQL Server 2014

Regular Expression Transformation for SQL Server 2016

Regular Expression Transformation for SQL Server 2017

Regular Expression Transformation for SQL Server 2019

Version History

SQL Server 2019

Version 7.0.2.91 – SQL Server 2019.
(29 Sep 2019)

SQL Server 2017

Version 6.0.2.91 – SQL Server 2017.
(29 Sep 2019)

SQL Server 2016

Version 5.0.2.91 – SQL Server 2016 GA release.
(25 Jul 2017)

SQL Server 2014

Version 4.0.0.90 – SQL Server 2014 release. Includes upgrade support for both 2005, 2008 and 2012 packages to 2014.
(6 Sep 2014)

SQL Server 2012

Version 3.0.0.87 – SQL Server 2012 release. Includes upgrade support for both 2005 and 2008 packages to 2012.
(5 Jun 2012)

SQL Server 2008

Version 2.0.0.87 – Release for SQL Server 2008 Integration Services.
(10 Oct 2008)

SQL Server 2005

Version 1.1.0.93 – Added option for you to choose AND or OR logic when multiple columns have been selected. Previously behaviour was OR only.
(31 Jul 2008)

Version 1.0.0.76 – Installer update and improved exception handling.
(28 Jan 2008)

Version 1.0.0.41 – Update for user interface stability fixes.
(2 Aug 2006)

Version 1.0.0.24 – SQL Server 2005 RTM Refresh. SP1 Compatibility Testing.
(12 Jun 2006)

Version 1.0.0.9 – Public Release for SQL Server 2005 IDW 15 June CTP
(29 Aug 2005)

Screenshots

Regular Expression Editor dialog 1 Regular Expression Editor dialog 2