Regular Expression Transformation

by Darren Green 11 Oct 2008 18:53

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\90\DTS\PipelineComponents\RegExTransform.xml.

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 to complete the installation, 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 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.

Regular Expression Transformation for SQL Server 2005

Regular Expression Transformation for SQL Server 2008

Version History

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

Tags regex, component, data cleansing, custom components, data validation, download

Categories Component Downloads | Transformations

RegexClean Transformation Use the power of regular expressions to cleanse your data right there inside the Data Flow. This t...Maintaining packages with code - Adding a property expression programmaticallyEvery now and then I've come across scenarios where I need to update a lot of packages all in the sa...Expression Date FunctionsDate Parts Expressions support a range of date related functions such as DATEADD, with the same bas...

Comments

2/20/2009 10:03:47 PM #

Great component, why this doesn't ship out of the box is beyond me, but I'm glad I found it. Pickup a regular expression cookbook, populate your xml file and this component saves SOoo much time it isn't even funny.

Thanks!

Garth H

3/4/2010 4:08:53 PM #

How can i use the same instance of the component to give me 3 output.

1)MatchedRows
2)Non-Matched Rows
3) ErrorOutput.

Rishi

3/19/2010 10:05:20 AM #

Great component. Will definitely use it.

Sandy

4/2/2010 8:15:08 AM #

Rishi - Non matched rows are directed to the alternate output. Do you want those to display in the same output along with the matched rows.

Excellent component. It really saves lot of time.

Vcloud

Add comment

Name*
E-mail*
Website
Country Country flag

b i u quote * Required fields. Your email address will not shown, check the preview for what you see. We use it to send you notifications on new comments if you ask us to below.

Comment
Preview

Notify me when new comments are added

Popular this month

Posts	Hits
The Execute SQL Task	308
Looping over files with the Foreach Loop	240
Streaming Profiler Data Through StreamInsight	127
For Loop Container Samples	109
File Watcher Task	106
Multicast Transform and Threading in SQL Server 2008	100
Easy Package Configuration	89
Row Number Transformation	89
Issuing Current Time Increments in StreamInsight (A Practical Example)	88

RecentComments

SQL Server Editions and Integration Services (3)
Slot Online wrote: Dude.. I am not much into reading, but somehow I g... [More]
Looping over files with the Foreach Loop (51)
Ragan Martin wrote: This article is excellent just what I needed. R... [More]
For Loop Container Samples (17)
MSK wrote: Awesome example with excellent explanation ... Kee... [More]
A deadlock was detected while trying to lock variables in SSIS (2)
Todd McDermid wrote: To support Darren's statement that this method has... [More]
Optimising Lookups with the Cache Transform (Video) (1)
khaled wrote: good [More]
Using Parent Package Variables in Package Configurations (1)
Diane Guggisberg wrote: Sorry but I still cannot get this to work and ever... [More]
Creating packages in code - OLE-DB Source to Flat File File Destination (8)
S Desai wrote: Hello, I want to create packages in code - OLE-... [More]
SSAS Cache Warming Using SSIS (13)
Stephen wrote: WHere can i download the package you built? This i... [More]
Row Number Transformation (19)
Darren wrote: Ravi, I would add an Execute SQL Task before the D... [More]
Row Number Transformation (19)
Ravi wrote: HI I have a question? I have already 100 or... [More]
Downloading a file over HTTP the SSIS way (19)
Naresh Jagtiani wrote: Hello Darren, Thanks for your reply. This is wha... [More]
File Watcher Task (61)
Darren wrote: gaurav, can you double check you have downloaded a... [More]
Trace File Source Adapter (2)
Darren wrote: Michael, hands up, your right we don't observe Val... [More]
Downloading a file over HTTP the SSIS way (19)
Darren wrote: Naresh, This work fine with SSL. Your error in... [More]

Comment RSS

SQL Server Integration Services - SSIS

Regular Expression Transformation