SSIS And Regular Expressions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS And Regular Expressions

Author

Topic

jhoge123
Starting Member

8 Posts

Posted - 2007-02-09 : 13:07:14

Hi,

I have a crappy old database that has an email field with a lot of bad data, and I need to use SSIS to extract a list of names & email addresses.

I have an OLE DB Source set up to pull the relevant columns out of the database. Is there a way that I could setup a Regular Expression based filter to disregard any rows where my email Regex does not match the value of the email column?

Thanks,
John

raclede
Posting Yak Master

180 Posts

Posted - 2007-02-13 : 20:08:11

Use this UDF in your query..

Regular Expression UDF
http://www.sqlteam.com/item.asp?ItemID=13947

Antonio
Posting Yak Master

168 Posts

Posted - 2007-02-14 : 11:52:36

quote:
Originally posted by jhoge123

Hi,

I have a crappy old database that has an email field with a lot of bad data, and I need to use SSIS to extract a list of names & email addresses.

I have an OLE DB Source set up to pull the relevant columns out of the database. Is there a way that I could setup a Regular Expression based filter to disregard any rows where my email Regex does not match the value of the email column?

Thanks,
John

You can either,

1) Use a UDF in a SQL Task comonent as has been suggested

OR

2) Follow the method suggested in the following blog : http://blogs.conchango.com/jamiethomson/archive/2005/04/03/SSIS_3A00_-Regular-Expressions-_2600_-Fuzzy-Matching.aspx

OR

2) Try this : http://www.microsoft.com/downloads/details.aspx?familyid=c16f11ad-150a-4091-b3a2-83d21d3e0973&displaylang=en

Hope this helps.

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

emjust
Starting Member

1 Post

Posted - 2009-12-21 : 22:10:36

I have created a new SSIS component called RegExtractor for pulling out regular expressions from a data flow.

It is free and open source.

Please visit:
http://regextractor.codeplex.com

SSISJoost
Starting Member

9 Posts

Posted - 2011-03-05 : 07:23:53

Here are two C# Script Component examples that use .Net Regular expressions.

- To validate (for example an email address):
http://microsoft-ssis.blogspot.com/2011/03/regular-expressions-in-ssis.html

- To replace/remove values (for example remove html tags):
http://microsoft-ssis.blogspot.com/2011/03/cleaning-with-regular-expressions-in.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources