SSIS Card number check

Question

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
*  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;
    }
}

Answer 1

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.html

---

http://www.rad.pasfu.com

Answer 2

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.

Answer 3

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

Answer 4

Hi i've put my code in the built in component but it still does not work.  I'm really struggling with this.  The components are not in my sql directory.

Microsoft.SqlServer.Dts.Pipeline.Wrapper is in C:\Windows\Microsoft.NET\assembly\GAC_MSIL

Microsoft.SqlServer.Dts.Runtime.Wrapper is in C:\Windows\Microsoft.NET\assembly\GAC_32 and C:\Windows\Microsoft.NET\assembly\GAC_64.

When i try to add them it says it cannot find them and i get this when i try to build.

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

This is one of the things i really HATE about visual studio.  It seems to be really flaky when referencing components.  How do i resolve this??? Thanks for your help.

Answer 5

Which version of SSIS are you using? 2008, 2008 R2, 2012

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 6

i'm using ssis 2008 r2 i've run a repair as well.  this is what it says i have installed in the installation setup.  I have SSMS 2012 installed so i haven't bothered installing SSMS for SQL 2008 but i need to support ssis packages created with 2005 and 2008.  How do i get it to reinstall those missing componenets???

Answer 7

You script component of 2008 R2 seems to reference a 2012 dll which isn't possible... (is newer .Net version which isn't supported by 2008 R2).

Path of reference should be something like this:

C:\Windows\assembly\GAC_32\Microsoft.SqlServer.DTSRuntimeWrap\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.DTSRuntimeWrap.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.DTSPipelineWrap\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.DTSPipelineWrap.dll

This is a 2012 dll
C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.DTSPipelineWrap\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.SQLServer.DTSPipelineWrap.dll

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 8

If i go to add reference in project explorer under the .net tab i have this.  When i try to browse and add an older one it just doesn't do anything. Please let me know what i'm doing wrong, this is so frustrating.

If i look in my C:\Windows\Microsoft.NET\assembly\GAC_MSIL it looks like i only have the lastest component from 2012.  How do i get the older versions back in here?

Answer 9

mmmmh me too. If I remove them, I can browse to them, but can't add them.

But they are referenced by default...

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 10

I have fixed the missing component issue!  i installed Client Tools Connectivity and Client Tools SDK and one of these has fixed the references!  I can build the solution without errors. 

Answer 11

i'm getting this when i try and run the code for some reason?!?  A nice vague microsoft error...

Answer 12

i'm getting this when i try and run the code for some reason?!?  A nice vague microsoft error...

Debugging isn't supported for Script Components (New in SSIS 2012). Here are some workarounds:
http://microsoft-ssis.blogspot.com/2011/04/breakpoint-does-not-work-within-ssis.html

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 13

yes i found that out after posting that message... thanks for your help! Do you know which component out of Client Tools Connectivity and Client Tools SDK would have fixed my issue?