THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Using Find/Replace with regular expressions inside a SSIS package

Another one of those might-be-useful-again-one-day-so-I’ll-share-it-in-a-blog-post blog posts

I am currently working on a SQL Server Integration Services (SSIS) 2012 implementation where each package contains a parameter called ETLIfcHist_ID:

image

During normal execution this will get altered when the package is executed from the Execute Package Task however we want to make sure that at deployment-time they all have a default value of –1. Of course, they tend to get changed during development so I wanted a way of easily changing them all back to the default value. Opening up each package in turn and editing them was an option but given that we have over 40 packages and we might want to carry out this reset fairly frequently I needed a more automated method so I turned to Visual Studio’s Find/Replace… feature

image

Of course, we don’t know what value will be in that parameter so I can’t simply search for a particular value; hence I opted to use a regular expression to identify the value to be change. In the rest of this blog post I’ll explain how to do that.

For demonstration purposes I have taken the contents of a .dtsx file and stripped out everything except the element containing the parameters (<DTS:PackageParameters>), if you want to play along at home you can copy-paste the XML document below into a new XML file and open it up in Visual Studio:

<?xml version="1.0"?>
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts">
  <DTS:PackageParameters>
    <DTS:PackageParameter
      DTS:CreationName=""
      DTS:DataType="3"
      DTS:Description="InterfaceHistory_ID: used for Lineage"
      DTS:DTSID="{635616DB-EEEE-45C8-89AA-713E25846C7E}"
      DTS:ObjectName="ETLIfcHist_ID">
      <DTS:Property
        DTS:DataType="3"
        DTS:Name="ParameterValue">VALUE_TO_BE_CHANGED</DTS:Property>
    </DTS:PackageParameter>
    <DTS:PackageParameter
      DTS:CreationName=""
      DTS:DataType="3"
      DTS:Description="Some other description"
      DTS:DTSID="{635616DB-EEEE-45C8-89AA-713E25845C7E}"
      DTS:ObjectName="SomeOtherObjectName">
      <DTS:Property
        DTS:DataType="3"
        DTS:Name="ParameterValue">SomeOtherValue</DTS:Property>
    </DTS:PackageParameter>
  </DTS:PackageParameters>
</DTS:Executable>

We are trying to identify the value of the parameter whose name is ETLIfcHist_ID – notice that in the XML document above that value is VALUE_TO_BE_CHANGED. The following regular expression will find the appropriate portion of the XML document:

{\<DTS\:PackageParameter[\n ]*DTS\:CreationName="[A-Za-z0-9\:_\{\}- ]*"[\n ]*DTS\:DataType="[A-Za-z0-9\:_\{\}- ]*"[\n ]*DTS\:Description="[A-Za-z0-9\:_\{\}- ]*"[\n ]*DTS\:DTSID="[A-Za-z0-9\:_\{\}- ]*"[\n ]*DTS\:ObjectName="ETLIfcHist_ID"\>[\n ]*\<DTS\:Property[\n ]*DTS\:DataType="[A-Za-z0-9\:_\{\}- ]*"[\n ]*DTS\:Name="ParameterValue"\>}[A-Za-z0-9\:_\{\}- ]*{\<\/DTS\:Property\>}

I have highlighted the name of the parameter that we’re looking for. I have also highlighted two portions identified by pairs of curly braces “{}”; these are important because they pick out the two portions either side of the value I want to replace, in other words the portions highlighted here:

<DTS:PackageParameters>
    <DTS:PackageParameter
      DTS:CreationName=""
      DTS:DataType="3"
      DTS:Description="InterfaceHistory_ID: used for Lineage"
      DTS:DTSID="{635616DB-EEEE-45C8-89AA-713E25846C7E}"
      DTS:ObjectName="ETLIfcHist_ID">
      <DTS:Property
        DTS:DataType="3"
        DTS:Name="ParameterValue">VALUE_TO_BE_CHANGED</DTS:Property>
    </DTS:PackageParameter>

Those sections in the curly braces are termed tag expressions and can be identified in the replace expression using a backslash and a number identifying which tag expression you’re referring to according to its ordinal position. Hence, our replace expression is simply:

\1-1\2

We’re saying the portion of our file identified by the regular expression should be replaced by the first curly brace section, then the literal –1, then the second curly brace section. Make sense? Give it a go yourself by plugging those two expressions into Visual Studio’s Find and Replace dialog. If you set it to look in “All Open Documents” then you can open up the code-behind of all your packages and change all of them at once. The Find and Replace dialog will look like this:

image

That’s it! I realise that not everyone will be looking to change the value of a parameter but hopefully I have shown you a technique that you can modify to work for your own scenario.

Given that this blog post is, y’know, on the web I have no doubt that someone is going to find a fault with my find regex expression and if that person is you….that’s OK. Let me know about it in the comments below and perhaps we can work together to come up with something better! Note that some parameters may have a different set of properties (for example some, but not all, of my parameters have a DTS:Required attribute) so your find regular expression may have to change accordingly.

When researching this I found the following article to be invaluable: Visual Studio Find/Replace Regular Expression Usage

@Jamiet

Published Tuesday, June 12, 2012 10:56 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jbooker said:

This could definately be handy.  Thanks for sharing.  I can think of an application for something similar.  Can't say how much time I've spent trying to figure out which variables are used in a script task but are missing from the component variables properties.  Of course the error output is no help.  Perhaps if the script code is saved in dtsx one could search the code for variables and compare to script task properties to discern which are missing.  Better yet, does SSIS 2012 script debugging feature alleviate this pain?

June 12, 2012 9:18 AM
 

jamiet said:

Hi J,

Yeah, that would indeed be useful. Perhaps a spot of XML parsing could help here: http://sqlblog.com/blogs/jamie_thomson/archive/2009/10/18/collecting-information-about-your-ssis-packages-ssis-nugget.aspx

Regards

Jamie

June 12, 2012 9:22 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement