In previous blog post I have written overview about SSIS Configuration (for beginners). The question I didn't answer was how to actually do it in SSIS. In this article I will describe how to perform SSIS Configuration so a package works on different environment using SSIS 2012 Parameters method which is a new configuration method.
I will give you overview of parameters and mention about variables but this blog post will provide only overview about parameters and show you SSIS 2012 screenshots from SSDT (Visual Studio) and later on I will include links to new articles that will describe parameters in more details.
SSIS 2012 Parameters Model
IMPORTANT: The content of this article may change (I still do some research). Feel free to suggest improvements.
My first approach to understand to SSIS Configuration Model using Parameters was to Google Microsoft Link about it which I found http://msdn.microsoft.com/en-us/library/hh213214.aspx but I must admit even with my experience I didn't understand a lot and as I am not a fan of "difficult reading" I decided not to spend to much time on reading it and do some more research, practice and write something on my own.
Parameters is quite a comprehensive subject so I decided to break it down into small chunks that are easier to digest so in this article I will give you overview of the new model from SSIS 2012 Development perspective (I will not discuss deployment and execution in details but check our SSIS Tutorial for more articles)
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.
Where to go next?
SSIS 2012 Project Parameters