Using Find/Replace with regular expressions inside a SSIS package
- by jamiet
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: 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 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:
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
