Sign In | Feedback | Donate | Search
Katie and Emil Logo
Vote Yes! I like it! [+0]  |  Vote no! Dislike? [-0]
Author: by Emil Glownia

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.

SSIS 2012 Parameters

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

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

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.

Take care

Emil

Where to go next?

SSIS 2012 Project Parameters

Upsss... Something went wrong and Google Ads on our website did not display.

Google ads sponsor FREE content on our website and without them it would not exist.

How to fix the problem?
  1. Check if the Google Search display any ads by searching the term Business Intelligence
    • If ads don't display, try different search terms and if there no ads then there might be a problem with your browser. If you made changes to your browser behaviour then undoing them will help.
    • If ads display then, refresh our page one more time, if the problem is still there than please contact us.
  2. Become 'No Ads Learner' Member or if you are already one Sign In.
Close
Take care
Katie & Emil
Well done! It seems Google Ads display properly now on our website.

Access our website's FREE content for FREE!