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.
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.
SQL Server 2019
Version 220.127.116.11 – SQL Server 2019.
(29 Sep 2019)
SQL Server 2017
Version 18.104.22.168 – SQL Server 2017.
(29 Sep 2019)
SQL Server 2016
Version 22.214.171.124 – SQL Server 2016 GA release.
(25 Jul 2017)
SQL Server 2014
Version 126.96.36.199 – SQL Server 2014 release. Includes upgrade support for both 2005, 2008 and 2012 packages to 2014.
(6 Sep 2014)
SQL Server 2012
Version 188.8.131.52 – SQL Server 2012 release. Includes upgrade support for both 2005 and 2008 packages to 2012.
(5 Jun 2012)
SQL Server 2008
Version 184.108.40.206 – Release for SQL Server 2008 Integration Services.
(10 Oct 2008)
SQL Server 2005
Version 220.127.116.11 – 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 18.104.22.168 – Installer update and improved exception handling.
(28 Jan 2008)
Version 22.214.171.124 – Update for user interface stability fixes.
(2 Aug 2006)
Version 126.96.36.199 – SQL Server 2005 RTM Refresh. SP1 Compatibility Testing.
(12 Jun 2006)
Version 188.8.131.52 – Public Release for SQL Server 2005 IDW 15 June CTP
(29 Aug 2005)