asked
ssis card number check
Hi i've come up with the following code for a SSIS script component. My programming is pretty weak so could someone 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... /* 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; } }
I'm getting these errors when i try to build however i'm not sure why... 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?)
Do you see the `Microsoft.SqlServer.DTSPipelineWrap` and `Microsoft.SqlServer.DTSRuntimeWrap` assembly in the References in the Solution Explorer, when you are editing the Script? If not, you will ahve to add references to those assemblies. Normally they are automatically included. Also there can be a prbolem with the installation. One thisng related to NULLing fields. You should use the **`Row.MobilePhote_IsNull = true`** to set NULL value in that particular field.
Seems so. I can only suggest to: 1. Define a RegEx varuable on the `ScriptMain` class Level: `Regex r;` 2. In the `PreExecute` event or directly on the Class level create an instance of the RegEx: `r = new Regex(sPattern, RegexOptions.IgnoreCase | RegexOptions.Compiled);` 3. Use the instance of the Regex: `Math One = r.Match(BusinessEmail);` This will avoid allocating the RegEx engine and processing the pattern for each row in the Buffer, but the RegEx will be allocated only once. You should receive a better performance.
sorry can you edit my code to make it more clear? I'm not very good with c# programming... thanks.
@Pavel Pawlowski I seem to also be having a problem where when i try to add a reference to the components it still says the referenced component could not be found. I am getting this Resolved file has a bad image, no metadata, or is otherwise inaccessible. Could not load file or assembly 'C:\Windows\
Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.DTSPipelineWrap\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.SQLServer.DTSPipelineWrap.dll' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded. sc_52c3585cbfb146e6a368ccf765351705 How do i fix this? Thanks for your help.
8 People are following this question.
