SSIS 2012 Parameters Model

SSIS Configuration objective is to make sure that the package can work on different environments and in SSIS 2012 this is achieved using new featured called parameters. Is this method new? Rather not, in previous version of SSIS (2005 to 2008 R2) we had variables in a package and 'package configuration' that allowed us to change variables from "outside".

The new method is nothing than break down of variables into:

• Parameters which are:
  • Project Parameters 
  • Package Parameters
• Variables (that stays the same but without functionality that was replaced by Parameters)

To give you a better picture of the new model look the picture below.

The major difference between SSIS 2005 up to 2008 R2 is that the model changes from 'package configuration' to 'project configuration' and that means that we now create a project and add packages and parameters to it. Package on it's own can no longer be used (unless you use 'legacy' option). Package MUST be part of a project which undoubtedly will spark a few discussion (pros and cons) but I presume best practice will soon appear.

Coming back to the subject what we now have are Parameters and make note there are two types of parameters which are:

Project Parameters - They live outside of the package on project level and you parameterize your packages using project parameters (which you can also think like Global variables or Global Parameters). Project Parameters will most likely be used for things that change between environment but don't change on the same environment (during execution) which means that majority of it will be connection strings. 

Below is a screenshot of SSIS 2012 in SSDT that shows Project Parameters

Package Parameters - We can also define Package Parameters and those live inside the package. Package Parameters work in a similar way as variable and the main difference is that when we execute a package for instance from another package we pass values to package parameters not directly to variables which in SSIS 2012 basically simplifies it in my opinion (as we don't have variables that do everything like in SSIS 2005 to 2008 R2)

Below is a screenshot of SSIS 2012 in SSDT that shows Package Parameters

Note to myself.... write article about Package Parameters (In depth) and provide link here

Variables - They work inside the package and often change during execution of the package (loops etc) but they no longer have responsibility of managing more static values which now are managed by package parameters and project parameters.

 Important: There is one very important aspect I didn't mention and that is how to change parameter value depending on environment which is the key element of SSIS Configuration. The reason why I didn't mention it is that you cannot do that during development, which is a good thing I believe and you can do that during deployment (or take development values which I don't recommend) or after deployment and I will dedicate separate articles on this subject. What you can do during development is specify parameter design values and you can have collection of values for different purpose which will make development life easier. These values can be deploy as 'final' but I do not recommend it as it miss the point of having parameters.... I will write more about it soon but I thought I will give you a quick answer here.

So that is high level view about parameters in SSIS 2012. It is not intended to give you in-depth knowledge or knowledge to build projects using  new model but hopefully it will give you better understanding of the new model and we will write new articles soon that will go into more details.