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.