0845 643 64 63

Pattern matching in SSIS using Regular Expressions and the Script component

One of my favourite features of SSIS is the script component, and I know I’m not alone. Why? Because it brings the entire might of the .NET framework to SSIS, providing C# (in SQL 2008 onwards) and VB.NET extensibility for all those times where SSIS doesn’t quite have enough functionality out of the box.

Such as?

Well a problem I’ve come across a number of times is string parsing. Trying to search for and extract a specific pattern of characters from a larger text string. I’ve seen developers build crazy convoluted expressions in the derived column transform, some of which are very impressive in their complexity! This is a bad thing not good, although it shows a level of prowess in building SSIS expressions (not the most intuitive expression language!), it becomes a support nightmare for other developers.

Take for example extracting a house number from the first line of an address, we want to convert “29 Acacia Road” into “29”

Or extracting a version number from a product, converting “iPhone 4G, IOS 4.3.2, black” into “4.3.2”

Or extract the html page from a URL, converting “http://www.wibble.com/folder/page.aspx” into “page.aspx”

Regular Expressions

The .NET framework includes full support for regular expressions (Regex), in the System.Text.RegularExpressions namespace. Regex provide an incredibly powerful way of defining and finding string patterns. They take some getting used to, but once you get the hang of them you can unleash their power in your SSIS dataflow pipelines.

To find out more about regular expressions, look at the following links

 

Let’s look at an example

Let’s take our first example from above, extracting a house number, converting “29 Acacia Road” into “29”.

The first thing we need to do is define our Regex search pattern. In this case we know that it must be at the start of the string, and must be an integer, with any number of characters 0-9.

The pattern for this is “^[0-9]+”, which is broken down as
    ^ means the start of the line
    [0-9] means any number
    + means 1 or more of the preceding item.
    i.e. 1 or more integers at the start of the line.

What if we wanted this to also cope with a single letter after the number? i.e. “221b Baker Street”

We can add “[A-Za-z]?” to our pattern, in which
    [A-Za-z] means any character A-Z in either upper or lower case
    ? means 0 or 1 occurrences of this

We should also add “\b” to the end of this, which indicates a word boundary. This means that 221b should be a whole word, not part of a larger sequence “221BakerSt”. We can wrap up the [A-Za-z]\b together into brackets so that the ? applies to the combination, so that any single letter must be the end of a word or it will be ignored. In this way “221BakerSt” will return 221, as will “221 Baker St”, whereas “221B Baker St” will return “221B”.

So our new pattern is “^[0-9]+([A-Za-z]\b)?”

You’ve probably gathered by now that regular expressions can get quite complicated. I’m not going to go into any more detail about them here, but hopefully this gives you some idea of what they can do. There’s plenty of reading on the web if you want to know more. You should also make use of the Regex expression tester in the link above – it will save you lots of debugging!

How do we use Regular Expressions in SSIS?

Well it turns out this is the easy bit, with the help of the script component.

Step 1 – Add a script component into your data flow pipeline, configure it as a data transform. I’m using C#, but you can use VB.NET if you want

Step 2 – Give the script access to the input and output columns

Open the script component and select the input field from the “Input Columns” screen, in this case “Address1”. This can be ReadOnly.

Go to the “Inputs and Outputs” screen and add an output column to “Output 0”. We want to set the datatype to string, length 10. This new field will contain the results of our Regex pattern matching.

Step 3 – Create the script code

Click on “Edit Script” on the Script screen which will open up Visual Studio.

Add a reference to System.Text.RegularExpressions at the top of the script


      using System.Text.RegularExpressions;

Then place the necessary code in the Input0_ProcessInputRow function.


    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        //Replace each \ with \\ so that C# doesn't treat \ as escape character
        //Pattern: Start of string, any integers, 0 or 1 letter, end of word
        string sPattern = "^[0-9]+([A-Za-z]\\b)?";
        string sString = Row.Address1 ?? ""; //Coalesce to empty string if NULL
 
        //Find any matches of the pattern in the string
        Match match = Regex.Match(sString, sPattern, RegexOptions.IgnoreCase);
        //If a match is found
        if (match.Success)
            //Return the first match into the new
            //HouseNumber field
            Row.HouseNumber = match.Groups[0].Value;
        else
            //If not found, leave the HouseNumber blank
            Row.HouseNumber = "";
    }


When you save and exit the script, any component downstream of the script component will have access to the new HouseNumber field.

Flog-Blog-Out

26 Responses to Pattern matching in SSIS using Regular Expressions and the Script component

  • This is fantastic. Thank You Sooooo much for this information. It’s very powerful and works really well

  • One question: How would you add anohter output Column “StreetName” using the same “Address1” in your C## code?

    Let assume that you want to ouptut the following columns.

    “HouseNumber”
    “StreetName”

    Where and how would you put that in your code?

    Thank you and sorry for my ignorance about C##

    F

  • Hi Fran

    That’s a great question thanks.

    You can output as many fields as you like from the code, just by adding more “Row.xxx = yyy;” lines.

    The first thing you need to do is add a second output column (as per the second screenshot in the post), for street you’d probably want a string 50 or similar.

    Then, in the code, just add another line at the end setting the new field to the value you want by using Row.StreetName = xxx;

    I’ve modified the code slightly to do this here…

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
    //Replace each with \\ so that C# doesn’t treat as escape character
    //Pattern: Start of string, any integers, 0 or 1 letter, end of word
    string sPattern = “^[0-9]+([A-Za-z]\\b)?”;
    string sString = Row.Address1 ?? “”; //Coalesce to empty string if NULL
    string sHouseNumber = “”;

    //Find any matches of the pattern in the string
    Match match = Regex.Match(sString, sPattern, RegexOptions.IgnoreCase);
    //If a match is found
    if (match.Success)
    //Return the first match into the new
    //HouseNumber field
    sHouseNumber = match.Groups[0].Value;

    Row.HouseNumber = sHouseNumber;
    Row.StreetName = sString.Replace(sHouseNumber, “”).Trim();

    }

    Good luck with it
    Alex

  • Hi Alex,

    Thanks for your help.
    I am using ssis 2005 , can you please help us to give the code in vb.net
    Thanks in Advance.
    Kumar

    • Hi Kumar

      No problem at all. It’s the same structure and code, just VB-ified

      1
      
      Imports System.Text.RegularExpressions
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      
      Public Overrides Sub Input0_ProcessInputRow(ByVal ROW AS Input0Buffer)
          Dim sPattern AS String = "^[0-9]+([A-Za-z]b)?"
          Dim sString AS String = ROW.Address1 & ""
       
          Dim MATCH AS MATCH
          MATCH = Regex.Match(sString, sPattern, RegexOptions.IgnoreCase)
          IF MATCH.Success THEN
              ROW.HouseNumber = MATCH.Groups(0).Value
          ELSE
              ROW.HouseNumber = ""
          END IF
       
      END Sub
  • Nice article very helpful , this is a passion of mine, here is another example a little older
    Kimball ETL (subsystem 1) – Data Profiling via SSIS Data Flow – Pipeline Column Data Profiling

    http://www.sqlservercentral.com/articles/Extract%2c+Transform%2c+Load+(ETL)/63792/

  • Great article very informative .

  • Hi Alex,
    Great article and thank you very much. I’m having an issue with a seperate class I created in the Vsta accessible through the Script button. Here is what I got:
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {

    string rawAddress = Row.address + “, ” + Row.city + “, ” + Row.st + ‘ ‘ + Row.zip;
    //System.Windows.Forms.MessageBox.Show(completeAddress);
    var parser = new AddressParser.AddressParser();

    var result = parser.ParseAddress(rawAddress); // <>

    //string stdAddress = result.StreetLine + “, ” + (result.SecondaryUnit != “” ? result.SecondaryUnit + @”: ” : ” “) + (result.SecondaryNumber != “” ? result.SecondaryNumber : “”);
    //System.Windows.Forms.MessageBox.Show(stdAddress);
    Row.Address = result.StreetLine;
    Row.Suite = result.SecondaryNumber;
    }

    The cursor just sits and the program doesn’t lock up or anything. Works perfectly in my other non SSIS projects. I have a separate class called AddressParser where I initialize my Regex Patterns. This class is in another namespace and I do have a using statement and explicitly call it as above. This class file exists within the Vsta project. I’d greatly appreciate any thought on this. Thanks a bunch.
    Anthony

    • Adding to the above, my line marker got wiped ot when I posted it, the cursor sits at the line var result = parser.ParseAddress(rawAddress);

    • Hmm interesting, there’s no reason why it should stall there – seems pretty standard to me.
      Have you put any logging into the AddressParser class to see what happens inside the ParseAddress function?

  • IF I GIVE INPUT ROW “”XXX””,XXX THE OUTPUT MUST BE THE SAME IN DATABASE TABLE HOW TO DO WITH SSIS PACKAGE USING 3RD COMPONENT FOR .CSV TO DATABASE TABLE

  • can you help me to convert a url to regular expression in windows form using c#

    • You can use exactly the same code, transplanted into a c# winform app. The difficult bit will be defining what the actual regex pattern should be for a URL.
      There’s no single right answer here, it depends on what you consider a valid url. i.e. which protocols are valid? (http, https, ftp, mailto, etc. etc.), whether you want to allow query parameters, etc. I’d recommend finding an existing pattern that someone else has created and tested, then adapt to your needs.

      I’ve just done a quick Google and the first link that comes up looks like a pretty good starting point for you…
      http://blog.mattheworiordan.com/post/13174566389/url-regular-expression-for-links-with-or-without-the

      Regards
      Alex

  • Great article ! This is fantastic. Thank You Sooooo much for this information. It’s very powerful and works really well – Parse Integration Services

  • Hi ,

    I have to perform several data checks before loading data into target table.

    Foe example I am having 1 flat file with below column

    Id Name Age
    Int Varchar(100) Int
    My requirement is to create package, checks will be performed on each record, column of the files.

    Any records which failed the checks considered as error records and will be written to the exception table.
    if i have character in int datatype column , it should get the error notification with line number
    Can any one help me to achieve the above scenario.is there any example on web , as I am searching the solution of above scenario.

  • I have checked everything three times; but when I attempt to compile the code in the SQL Server side of things; I get an error saying: ‘ScriptMain.CreateNewOutputRows()’: no suitable method found to override @ line 135

    This line is: public override void CreateNewOutputRows()

    • Hi Keith
      The CreateNewOutputRows() function is only relevant when the Script component is configured as a source, not a transformation.
      When you create the transform you need to select either source, destination or transformation. The code in this post relates to a transformation, not source.
      Regards
      Alex

  • Can any one tell me the logic that how can i parse USA addresses by c#.

  • Hi Alex,

    This is a great code, helped me a lot. However I am getting an exception while trying to execute the SSIS package. Actually I trying to format PhoneNumbers using regex. The Input column is from a sql table and may have some Null values, therefore I am getting an exception as

    Script Component has encountered an exception in user code:
    Project name: —–
    Value cannot be null.
    Parameter name: Input
    at System.Text.RegularExpressions.Regex.Match(String input)
    at System.Text.RegularExpressions.Regex.Match(String input, String pattern, RegexOptions options)
    at ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
    at UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
    at UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)
    at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

    Please help me to fix this.

    • You just need to replace nulls with empty strings and it should fix the problem.
      You can either do this in the source query, or in a derived column task in the SSIS data flow, or in the C# script, all will work equally well.

  • Hi Alex is there a way to select a field from sql and put that in the regular expression like var sql = Select *
    And the string sPattern = “^sql+[0-9]+([A-Za-z]\\b)?”;

    • If you wanted to embed a field value into the RegEx then yes, just select the relevant field in the input columns of the script task, then you can refer to the contents of that column within the script.
      So just build a RegEx pattern dynamically in a C# string using the field contents, something like:
      string RegEx = “^” + Row.MyField + “+[0-9]+([A-Za-z]\\b)?”;

  • I am curious about how you would use this to extract 0 to many IP addresses from a string of text, such as might be found in a log file reporting connections to a server.

    • Hi Lynn
      You just need to define a regular expression that matches the IP address (& delimiter). Then just loop through all the matches.
      SSIS data flows require a fixed pre defined set of columns, so either define more columns than you need, or you could generate multiple rows each with a single address; it depends how you need the data structured.
      Regards
      Alex

  • Hello Alex,

    i am having a sql column having comment data as shown below:

    03/06/1995 10:39 AM Opened file and called employee for update and requested medical.

    06/01/1995 09:12 AM 2nd request for medical report. Employee still off complaining of pain in right hip and difficulty walking. Scheduling appt with Dr. Kevorkian for late June.

    07/15/1995 10:42 AM Employee mysteriously died following consultation with physician. Not thought to be related to this accident. Will document and keep file open pending outcome of autopsy.

    07/10/1996 10:46 AM Received summons today alleging employee’s death job related. Referred to counsel.

    All the above rows are in a single column. As we can see, there were comments added on 4 different dates, but they keep on appending in the same sql column.

    i need to parse them out and make 4 rows of this single row of data. (one row for each date and comment text).

    Is it possible to do this using regular expressions?

    any help would be highly appreciated.

    Thanks,
    Rahul

    • Firstly you need an asynchronous script transform, where the output is a different data buffer than the input. You can then generate as many output rows as you want from each input row.
      Then you just need to determine how to split the data up.

      Then yes you can use RegEx to find each of the matches, using a pattern similar to this:
      [0-9]{2}/[0|1][0-9]/[1|2][0-9]{3} [0-2][0-9]:[0-9]{2} (A|P)M .*

      i.e. a date, then time, then any number of characters until a line break.

      Alex

Leave a Reply

Your email address will not be published. Required fields are marked *

HTML tags are not allowed.

343,413 Spambots Blocked by Simple Comments

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon