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%\Microsoft SQL Server\nnn\DTS\PipelineComponents\RegExTransform.xml, where nnn represents the folder version, 90 for 2005, 100 for 2008 and 110 for 2012.
If you want to use regular expressions to manipulate data, rather than just validating it, try the RegexClean Transformation.
The component is provided as an MSI file, however for 2005/200 you will have to add the transformation to the Visual Studio toolbox by hand. This process has been described in detail in the related FAQ entry for How do I install a task or transform component?, just select Regular Expression Transformation in the Choose Toolbox Items window.
Downloads
The Regular Expression Transformation is available for SQL Server 2005, SQL Server 2008 (includes R2) and SQL Server 2012. Please choose the version to match your SQL Server version, or you can install multiple versions and use them side by side if you have more than one version of SQL Server installed.
Regular Expression Transformation for SQL Server 2005
Regular Expression Transformation for SQL Server 2008
Regular Expression Transformation for SQL Server 2012
Version History
SQL Server 2012Version 2.0.0.87 - SQL Server 2012 release. Includes upgrade support for both 2005 and 2008 packages to 2012.
(5 Jun 2012)
SQL Server 2008Version 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
