In this example We are going to show, How to create or configure SSIS Project Parameters and SSIS Package Parameters. And also show you the difference between Project Parameters and Package Parameters
SSIS Project Parameters Vs SSIS Package Parameters
In SSIS, Parameters are of two types: Project Parameters, and Package Parameters
- Project Parameters are something like Global Parameters. You can access them from any package present in that project.
- Package Parameters are similar to local variables. You can access them from the package that we created. You can’t access the package parameter in pakage1.dtsx from the pakage2.dtsx
SSIS Project parameters
In this example we will demonstrate the steps involved in create project parameters in SSIS. If you look at the Solution explorer, we have Project.params.
Right click on the Project.params and select the View Designer option from the context menu to create SSIS project parameter.
When you click on the View Designer option, following window will be opened. You can use this window to see the existing project parameter, or to create new project parameter, or to edit the existing one, or to delete.
From the below screenshot, we are creating new project parameter by clicking the New parameter button.
We assigned the name as ProjectMessage, Data Type as string, and value = Hey!! This is SSIS Project parameter.
Please click Save button to save the project parameter.
Now, You can use this Project parameter i.e., ProjectMessage in all the packages present in the SSIS Packages folder.
SSIS Package parameters
In this example we will demonstrate the steps involved in create package parameters in SSIS. To do so, let me create a new package.
If you observe the below screenshot, For every new package, it has a Parameters tab. You can use this tab to create package level parameters. Please click on this tab to create parameters.
The interface for creating package level, and project level parameters is same. Please click on the New parameter button to create new.
As you can from the below screenshot, we created a new parameter called PackageMessage of type string, and its value is Hi!! This is a Package message.
SSIS Project Parameters Vs SSIS Package Parameters
In this example we will use the Script task to display the Message that contains the data from both the Project Parameter, and the package parameter. To do so, Drag and drop the Script Task from SSIS toolbox into the Control Flow region as we shown below.
Double click on the Script task will open the following editor to configure the Script task components. We already explained the Script task configuration steps in our previous article so please refer the same.
Here we want to select the already created parameters so, Please click the … (Browse) button beside the Ready Only Variables property.
Here we are selecting both the Project Parameter, and package parameter as Read Only variables.
Once you selected the required variable, please click on the Edit Script.. button to write the actual C#, or VB Script
Here we are using Message Box to display the message in pop up.
C# code we used in the below screenshot is:
|
1 2 3 4 5 6 7 8 |
// SSIS Project Parameters Vs SSIS Package Parameters Example string ProjectMessage = Dts.Variables["$Project::ProjectMessage"].Value.ToString(); string PackageMessage = Dts.Variables["$Package::PackageMessage"].Value.ToString(); MessageBox.Show("Message From Project Parameter is:" + ProjectMessage + Environment.NewLine + Environment.NewLine + "Message From Package Parameter is:" + PackageMessage); |
Click OK to finish configuring the package. Let us Run the package
Let me open another package within this project. From the below screenshot, you can see this is a package that we create in Execute SQL Task in SSIS Single Rowset article.
Let me try to access the parameters that we created earlier from this script task. As you can see from the below screenshot, we are able to access the Project parameter but not the package parameter.
Thank You for Visiting Our Blog
Leave a Reply