Package Parameters and Project parameters in SSIS

A parameter, like a variable, is a placeholder that has a name, data type, scope, and value.SQL 2012 introduced a new parameter paradigm. Whit the help of Parameters we are able to pass in new values for a specific package execution. When our packages are deployed to the new SSIS , an interface is provided to enable us to change the values of parameters prior to running the package. Parameters are very similar to variables, except parameters are easier to change and configure using the new SQL Server Management Studio interface for SSIS.

Parameters also have a scope. Parameters can have a package scope or a project scope. A parameter with a project scope can be used in all packages within the project. Package scoped parameters can only be used within the package in which they are defined.

There are two types of parameters

Ø  Package Parameters

Ø  Project parameters

Package Parameters

Package parameters are new with SSIS 2012. The scope of a package parameter is the individual package where it resides. We can to use a package parameter when, at run-time, the value is different for each package. 

Here I am creating a simple package where I am importing the data from a table and storing that in the flat file.

Taking Data Flow Task

  

Double click on the Data flow task

In Data flow I am taking the Source as OLEDB. Now I am Configuring the required configuration.

  

Now I am taking the flat file to store the result.

  

I am creating variable to store the file name and location. Right click on the Flat File Connection Manager and click on the Properties.

Now I am creating the Configuration for flat file.

Now Click on the Expression

  

Select Properties as connection string

Click ok.

Now package is ready to run.

Before executing the package

There is no file in this folder.

Now I am executing the package.

Package executed successfully.

 This package is created on the Dev Environment. Suppose I we want to move this package to QA, CERT and finally Prod Environment. Then we need to change the configuration. In SSIS we can create the Different Environment.

Creating Different Environment in SSIS

By default package is in development Environment.

Suppose we want to create different Environment then we need to click on the Drop down.

Click on the Configuration Manager.

Click on the New

Write the Environment name. If we want to copy the setting of any environment then select the Environment. And Click ok.

I have created four environments.

Now I am creating the parameters.

Click on the parameters Tab.

Click on the new variable. and we need to configure the value of each environment.  

  

Click on the Add parameters to configuration tab.

Click on the Add button.

Select the file path. And click ok.

Here we are configuring the file path value according to the environment. Click ok.

Keep in Mind the configuration are applied only when packages are executed in Visual studio.

New right click on the Flat file configuration.

See the Parameter is start with $Package:: variable name.

Click ok.

Before executing the package.

  

Executing the package in dev

See the result

  

See now I am changing the environment. Running it on QA

See the result.

Similarly we can run on the all environments.

Project parameters

Project Level Parameters are available to all the packages in the project.

Click on the Project.Params

Here we can add the project level parameter

Adding a new variable

Now we need to add this on all environments.

Click on that

 

Add Project file path.

Click ok. New we need to configure the connection string value of flat file destination.

New we can execute the package.

Package executed successfully and gets the expected result.

See here now I am creating a new package and let’s see either we can use the

 

For checking the project level parameter I am taking Expression task

 

Double click on the Expression task.

 

See in Expression builder we are able the see only project label parameters.