Hi i've come up with the following code for a SSIS script component. My programming is pretty weak so could someone help me and check this for me and let me know if there is a better way of doing it? It basically just looks for a regex in specified columns and nulls them if it finds anything... Thanks! This is to remove any card numbers that get stuck in phone or email columns in a dataflow... I am also getting errors when i try to build it... Error 1 The type or namespace name 'Wrapper' does not exist in the namespace 'Microsoft.SqlServer.Dts.Pipeline' (are you missing an assembly reference?) Error 2 The type or namespace name 'Runtime' does not exist in the namespace 'Microsoft.SqlServer.Dts' (are you missing an assembly reference?) /* Microsoft SQL Server Integration Services Script Component * This is CozyRoc Script Component Plus Extended Script * Write scripts using Microsoft Visual C# 2008. * ScriptMain is the entry point class of the script.*/ using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; using System.Text.RegularExpressions; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { public override void Input0_ProcessInputRow(Input0Buffer Row) { //Replace each \ with \\ so that C# doesn't treat \ as escape character //Pattern: Card matching Switch, Solo, Visa, MasterCard, Discover, American Express string sPattern = "^((67\\d{2})|(4\\d{3})|(5[1-5]\\d{2})|(6011))-?\\s?\\d{4}-?\\s?\\d{4}-?\\s?\\d{4}|3[4,7]\\d{13}$"; string BusinessEmail = Row.BusinessEmail; string BusinessPhone = Row.BusinessPhone; string HomePhone = Row.HomePhone; string MobilePhone = Row.MobilePhone; string PersonalEmail = Row.PersonalEmail; //Find any matches of the pattern in the string Match One = Regex.Match(BusinessEmail, sPattern, RegexOptions.IgnoreCase); Match Two = Regex.Match(BusinessPhone, sPattern, RegexOptions.IgnoreCase); Match Three = Regex.Match(HomePhone, sPattern, RegexOptions.IgnoreCase); Match Four = Regex.Match(MobilePhone, sPattern, RegexOptions.IgnoreCase); Match Five = Regex.Match(PersonalEmail, sPattern, RegexOptions.IgnoreCase); //If a match is found set field to null if (One.Success) Row.BusinessEmail = null; if (Two.Success) Row.BusinessPhone = null; if (Three.Success) Row.HomePhone = null; if (Four.Success) Row.MobilePhone = null; if (Five.Success) Row.PersonalEmail = null; } }

why you don't use built-in Script component? as I see you used third party component: This is CozyRoc Script Component Plus Extended Script If you want to use Regex then you can use it in the built-in script component. here you can see a sample of using script component with Regex: http://www.rad.pasfu.com/index.php?/archives/53-Regular-Expression-in-SSIS.htmlhttp://www.rad.pasfu.com

Make sure you have added a dll reference under "Project >References" to the Microsoft.SqlServer.DTSPipelineWrap.dll and Microsoft.SQLServer.DTSRuntimeWrap.dll in your project. This assemblies can be located on your machine at the path: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies Or C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies based on the windows 32 or 64 bit version. here "100" should be replaced with "90" or "80" based on your SQL server version.

I agree with Reza. I don't see the point (for this specific case) here for using that custom script component. I think you have to add those references manually for this custom script component... you could ask the manufacturer: http://www.cozyroc.com/contact Here is another Regular Expression example: http://microsoft-ssis.blogspot.com/2011/03/regular-expressions-in-ssis.html PS C# has a @ for escaping a whole string: Eg. "c:\\my\\file.txt" could be done as @"c:\my\file.txt" Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter