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

Parameters in SSIS in Denali

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:

  1. Design Default: The value that gets set for a parameter at design-time
  2. Server Default: Optional new default value that is applied to the parameter when it is deployed to a catalog.
  3. 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:

image

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:

image

selecting that option displays the Project Parameters pane:

image

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.

Published Thursday, November 11, 2010 8:58 PM 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

 

Davide Mauri said:

Parameters was also a missing feature for me, that's why I've introduced them in my DTLoggedExec tool:http://dtloggedexec.davidemauri.it/Package%20Parameters.ashx. Actually is the only way to have them right now also with 2005 and 2008.

November 11, 2010 4:49 PM
 

Ed Gillett said:

Well it's about damn time ... :)

November 12, 2010 11:56 AM
 

jamiet said:

Ed,

Touché :)

November 12, 2010 12:03 PM
 

SSIS Junkie said:

In my blog post of 10th November Introduction to SSIS projects in Denali I spoke of how, in Denali, SSIS

November 12, 2010 1:41 PM
 

SSIS Junkie said:

In the last few days I have written 3 blog posts covering some new features in SQL Server Integration

November 13, 2010 1:50 PM
 

Hennie de Nooijer said:

Hi jamie,

On a other post of you i've already commented about the package configurations and parameters. Currently i've a package with package configuration with database connecties, parent package variables, environmentvariables, etc. For me it's not quite clear how to replace those by the new Denali parameters (yet)!

November 23, 2010 8:19 AM
 

Dattatrey Sindol said:

Hi Jamie -

You mentioned above that the existing Package Configurations feature of SSIS is going to continue ("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."). However, when I checked the SSIS menu in Denali, I don't get an option for "Package Configurations".

Please let me know if I am missing something or basically how do we go about creating configuration files in Denali SSIS.

Regards,

Datta

November 24, 2010 2:58 AM
 

Hennie de Nooijer said:

@Dattatrey: The package configurations will be enabled when you choose configurations in the properties of the package.

November 24, 2010 7:36 AM
 

Hennie de Nooijer said:

i'm trying to understand the concept of the catalog. A project is deployed at the catalog. Multiple environments can be linked with a package. But how do you handle DTAP? It's possible to have a package in development phase and one in production. How do you handle this in the catalog? Creating two packages seems ridiculous. Or is Development BIDS and SSMS deployment? Seems odd to me too.

November 24, 2010 7:44 AM
 

jamiet said:

===============================

Hennie,

" i've a package with package configuration with database connecties, parent package variables, environmentvariables, etc. For me it's not quite clear how to replace those by the new Denali parameters"

Hopefully it will become clear when you get to use it but in short anything that you used to set with a configuration should now be a parameter.

===============================

Dattatrey,

If you right-click on a project in Solution Explorer you will see an option to revert to the Legacy Deployment Model. If you select that then the Package Configurations menu item that you know and love should appear.

===============================

Hennie,

I don't know what DTAP stands for, sorry.

You're saying that there are multiple versions of a project, right? One that is in production and another that is in development, and you want to manage them on the same SSIS Catalog?

Well, the SSIS Catalog does allow for project versioning *but* I'm not sure that it is possible to execute an old version of a project (I need to look into this in more detail). Regardless of that I suspect that best practice will dictate that you have a dedicated SSIS Catalog for dev/test/prod anyway to enable clean separation of your environments.

Hope that helps.

-Jamie

November 24, 2010 8:11 AM
 

Hennie de Nooijer said:

Okay jamie. Denali CTP1 seems not to be completed yet regarding the SSIS deployment. For instance it's not possible running a package from the catalog in the SQL Server agent jobs with the GUI.

DTAP = Develop, Test, Acceptance and Production.

You're saying that there will be multiple Catalog's on the same server? or multiple Servers? Creating environments in the catalog assumes that there are multiple environments on the same server and when i am thinking further on, how about different versions of the packages in the catalog? This isn't implemented yet.

It seems to me that versioning as build right now is not meant as running different verisons of the package in different environments. It's possble to restore a project (not package) so that seems not option for versioning.

November 24, 2010 8:35 AM
 

jamiet said:

Hennie,

Yes, its definitely not complete. That's why they call it a preview :)

"You're saying that there will be multiple Catalog's on the same server? or multiple Servers?"

Sorry, I'll clarify. There can only be one Catalog per SQL Server instance - that is set in stone. I wondered the same thing myself and hence have clarified this with the product team via email. A Catalog can have multiple Environments.

"Creating environments in the catalog assumes that there are multiple environments on the same server"

The use of the word "enviuronments" could get confusing couldn't it? Maybe we should use "Environment" (with a capital 'E') to refer to an Environment object on the SSIS Server whereas "environment" (lower case 'e') refers to your server farms/network topology etc...

Anyway, I don't necessarily agree that multiple Environment objects per Catalog *assumes* there are multiple environments, it only means that this sort of topology is supported. Just because you *can* does not mean that you *should* and hence why I say I suspect most people will have a SSIS Catalog in each of their environments (note lower case 'e'). I'm sure there are scenarios where an environment can have multiple Environment objectss and equally I'm sure there are scenarios where folks will use one Catalog for ALL their environments (chief reason would, I suspect, be cost).

As an aside, perhaps "Environment" is the wrong word to use to describe these objects although the only word I can think of that would be better is "configuration" and that means something else in SSIS parlance.

Re: versioning, The versioning story seems a little murky right now - I suspect that will crystalise as we progress toward release. I do think we need the ability to execute a prior version.

Thanks for the comments. This is a good, interesting discussion.

-Jamie

November 24, 2010 8:53 AM
 

Dattatrey Sindol (Datta) said:

Thanks Hennie.

But as far as I remember, this was not the case in SQL Server 2008/R2. Navigation to Package Configurations in 2008/R2 was better I guess :)

Regards,

Datta

November 26, 2010 8:24 AM
 

Ayyappan said:

It is excellent feature. MSFT is standardizing ETL tool.

December 15, 2010 6:26 AM
 

SSIS Junkie said:

SQL Server Integration Services (SSIS) in its 2005 and 2008 incarnations expects you to set a property

December 20, 2010 12:35 PM
 

SSIS Junkie said:

The third Community Technology Preview (CTP3) of SQL Server code-named Denali is upon us and, although

July 12, 2011 4:32 PM
 

SSIS Junkie said:

In recent blog posts I have introduced the new SSIS Catalog that is forthcoming in SQL Server Code-named

October 16, 2011 10:57 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

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