In this article I will describe SSIS 2012 Project Parameters and give you several examples.
SSIS Project Parameters
If you are new to SSIS 2012 Parameters than we suggest to read SSIS 2012 Parameters Overview
Project Parameters allows us to access the value of a specific project parameter from any SSIS package that is part of the same SSIS Project. Project Parameters are used to pass values to package where a value needs to change depending on the environment (developer, test or production server).
In this article I will show you how to create project parameter describe each column separately. I will also provide links to other articles that will give you more info about specific column settings or more examples.
This article will focus on Project Parameters using SSDT so I will not discuss deployment and configuration outside of SSDT which is an important aspect but later on I will try to add links that discuss it in more details.
Create Project Parameter
Let's start from creating a Project Parameter. I have created a project SSIS 2012 Project Parameters and in Solution Explorer (see below) we have Project.params that I double click to get a new window (left side).
I Click Create New Project Parameter button and get a new row with 6 columns that I will describe. See below picture.
Project Parameter Columns
- Name - This is simply the name of Project Parameter. Note Name seems to be CASE SENSITIVE so we can create TestParameter and testParameter and these will be created and treated as different parameters so make sure you follow the same standard.
- Data Type - Chose the data type for the value you want to store in the Project Parameter. Most data types are available apart from those that are not applicable to parameters like data type Object.
- Value - We can specify the value. Value is actually quite interesting as we can create different configuration during development (only) and specify the value and easily switch between different configuration. When using SSDT these are called Design Values.
- Sensitive - Initially I thought maybe this is something to do with case sensitivity BUT NO! Sensitive column indicates if the value you provided is sensitive, in other words do want to protect the value. If you chose True than the value will be encrypted. In SSDT we will get ****** (see below) and when we deploy the project and try to query the values the fields value will be NULL.
- Use sensitive TRUE when you store credentials (username, password) or any information that potentially is harmful if it gets into wrong hands. Personally I think it is very good option but I can imagine that developers will have to organize themselves better and manage passwords in more structured way (It is just a matter of time before someone ask on a forum how to retrieve credentials as they have lost it ;) .... some developers will just set to FALSE for every parameter, which I don't think is a bad thing in some teams where this level of security (or standard) is not mandatory.
- Personally I think this column should be called Protect or Encrypt rather than Sensitive...
- Required - This is another confusing field name. Required is related to Deployment if it is set to False then the value you currently have set will be used during deployment (unless you overwrite it). If you set it to TRUE and perform deployment than the value (Design value) will NOT be populated during deployment (unless you specify it explicitly).
- Default value is FALSE but personally I intend on setting it always to TRUE to avoid accidental values. In previous version of SSIS design/development values could potentially be used by accident which I didn't like at all so to take advantage of new model and avoid previous issues than I would recommend it to set Required to TRUE.
- To better understand this important behavior please see our example in the following blog post SSIS 2012 Parameter Required Column
- Description - Finally easy access to provide some description about the purpose of the parameter and extra notes.