SSIS Junkie : Parameters

Using Find/Replace with regular expressions inside a SSIS package

jamiet — Tue, 12 Jun 2012 08:56:37 GMT

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

Required Parameters [SSIS Denali]

jamiet — Mon, 20 Dec 2010 17:35:10 GMT

SQL Server Integration Services (SSIS) in its 2005 and 2008 incarnations expects you to set a property values within your package at runtime using Configurations. SSIS developers tend to have rather a lot of issues with SSIS configurations; in this blog post I am going to highlight one of those problems and how it has been alleviated in SQL Server code-named Denali.

A configuration is a property path/value pair that exists outside of a package, typically within SQL Server or in a collection of one or more configurations in a file called a .dtsConfig file. Within the package one defines a pointer to a configuration that says to the package “When you execute, go and get a configuration value from this location” and if all goes well the package will fetch that configuration value as it starts to execute and you will see something like the following in your output log:

Information: 0x40016041 at Package: The package is attempting to configure from the XML file "C:\Configs\MyConfig.dtsConfig".

Unfortunately things DON’T always go well, perhaps the .dtsConfig file is unreachable or the name of the SQL Sever holding the configuration value has been defined incorrectly – any one of a number of things can go wrong. In this circumstance you might see something like the following in your log output instead:

Warning: 0x80012014 at Package: The configuration file "C:\Configs\MyConfig.dtsConfig" cannot be found. Check the directory and file name.

The problem that I want to draw attention to here though is that your package will ignore the fact it can’t find the configuration and executes anyway. This is really really bad because the package will not be doing what it is supposed to do and worse, if you have not isolated your environments you might not even know about it. Can you imagine a package executing for months and all the while inserting data into the wrong server? Sounds ridiculous but I have absolutely seen this happen and the root cause was that no-one picked up on configuration warnings like the one above.

Happily in SSIS code-named Denali this problem has gone away as configurations have been replaced with parameters. Each parameter has a property called ‘Required’:

Any parameter with Required=True must have a value passed to it when the package executes. Any attempt to execute the package will result in an error. Here we see that error when attempting to execute using the SSMS UI:

and similarly when executing using T-SQL:

Error is:

Msg 27184, Level 16, State 1, Procedure prepare_execution, Line 112
In order to execute this package, you need to specify values for the required parameters.

As you can see, SSIS code-named Denali has mechanisms built-in to prevent the problem I described at the top of this blog post. Specifying a Parameter required means that any packages in that project cannot execute until a value for the parameter has been supplied. This is a very good thing.

I am loathe to make recommendations so early in the development cycle but right now I’m thinking that all Project Parameters should have Required=True, certainly any that are used to define external locations should be anyway.

@Jamiet

Parameters in SSIS in Denali

jamiet — Thu, 11 Nov 2010 19:58:00 GMT

In my last blog post Introduction to SSIS Projects in Denali I talked about the new Project deployment model that is coming in the next version of SQL Server Integration Services (SSIS); working hand-in-hand with Projects is another new feature – Parameters. Parameters are similar to Variables in SSIS today that we all know and (ahem) love but with one important difference – they are fundamental to how SSIS will manage and execute packages inside a SSIS Catalog (we’ll get onto those later) and hence have some subtle differences to Variables. Moreover (this should please a lot of you), parameters are the replacement for configurations that are in the current version of SSIS (i.e. SSIS2008).

There are two types of parameters:

Project parameters
Package parameters

I’ll be covering both herein.

First there is a little bit of terminology to be grasped in regard to parameters. As with all terminology please learn these and use them appropriately:

Design Default: The value that gets set for a parameter at design-time
Server Default: Optional new default value that is applied to the parameter when it is deployed to a catalog.
Execution Parameter Value: Effectively an override for the Server default that you set when the package is executed

There are a few more things to know about Parameters in regard to Catalogs and Environments (another new term) but as I haven’t talked about Environments yet I’ll save that for later.

Parameters can be read from and written to inside a package and by implication they can also be referenced by any task in those packages. References will be in an expression just as is the case with Variables, they just happen to have a slightly different syntax as shown here:

Simply a prefix of either $Project or $Package (as appropriate) can be applied to a parameter name to reference it in an expression. I have highlighted a Package Parameter and two Project Parameters available in the familiar Expression Builder in the screenshot above.

Project Parameters

As I said above Parameters are very similar to Variables but in the case of Project Parameters there is one very important characteristic that distinguishes them - instead of being scoped to a package they are scoped to a Project and any package within that project can access them. (This is why its important to have a good grasp of SSIS Projects before being introduced to Parameters and hence why my first post in this series was Introduction to SSIS Projects in Denali). With that in mind let’s take a closer look at Project Parameters.

Right-clicking on a project in Solution Explorer will offer the option to display the parameters of that project:

selecting that option displays the Project Parameters pane:

In the example shown here I have a CustomerId parameter that can be accessed by any package within the project.

Scope, in the case of Project Parameters, is always “Project”'
Data Type is, hopefully, self-explanatory
Default Value is the Design Default that I mentioned earlier
Setting Sensitive on server to TRUE will ensure that the the value in the parameter gets encrypted when it is deployed to a SSIS catalog (important for secure credentials)
Setting Required to TRUE means that after the project is deployed a Server Default or Execution Parameter Value must be supplied.

The ability to be accessed across multiple packages in a project is important. SSIS developers today will be well used to various mechanisms to using a value in multiple packages; Parent Package Configurations are a common option as is referencing the same configuration file from multiple packages although, judging by the amount of posts on the SSIS forum, neither of these options are particularly well-liked amongst the SSIS fraternity. Writing to a Variable from multiple packages is even more difficult, indeed sometimes the only way to do it means resorting to writing script code – not quick and easy at all. Project Parameters change all that because they are defined outside of a package hence can be accessed by any package within the Project and this makes them ideal for storing such things as database connection strings. its not hard to see why they are going to be a great replacement for configurations.

Package Parameters

Pretty much everything I just said about Project Parameters applies to Package Parameters except for one important difference and that is implied by the name – they are scoped to a Package rather than a Project. If you’re now asking yourself “What’s the difference between Package Parameters and package-scoped Variables?” then you’re not alone, I was asking myself the same thing. Aside from what I already said about Project Parameters (able to be referenced inside a Catalog etc…) the distinguishing characteristic (and what makes them useful) as far as I can discern is that they can be referenced from an Execute Package Task thus solving the problems that I outlined in my Connect submission Execute Package Task should support parameters – indeed someone from the SSIS team replied to that submission by saying:

We have introduced parameters in upcoming release, and parent package will be able to use parent parameters to set the child package parameter values. Child package will also be able to run on its own. Hopefully this will address most of the issues described in this bug.

For folks that don’t want to move to the new deployment model (which isn’t a pre-requisite to using SSIS in Denali by the way) the ability to parameterize the Execute Package Task is a great new feature. More on the new Execute Package Task in a later blog post.

I think I have exhausted SSIS Parameters for now. In this blog post I introduced some terminology that I haven’t covered yet, namely Catalogs and Environments. I’ll cover those in a later blog post.

@Jamiet

UPDATE: I want to make a clarification about a statement I said above that some people may have misinterpreted. Configurations as you know them today are not going away -they are fully supported in Denali- now though you have the option to use parameters instead. So, for those of you that may be fretting that your existing SSIS implementations will not work in Denali, fear not - barring any other unforeseen mishaps they should continue to work just fine.