Getting Started with SQL Server Integration Services SSIS Parameters vs. Variables
I noticed that in SQL Server 2012 and later versions it is possible to create SQL Server Integration Services (SSIS) parameters. What are SSIS parameters and how are they used?
SSIS parameters are a feature introduced in SQL Server 2012. You can now pass values at a project level and at a package level. In the past, we used package configurations to store values in XML files or environment variables. Now we can use parameters to store our information.
- SQL Server Relational Database Engine installed
- SQL Server Integration Services installed
- SQL Server Data Tools Installed (SSDT) or BIDS (Business Intelligence Development Studio) installed
- You can use SQL Server 2012 or later versions. In this example, we are using SQL Server 2014
- A SQL Server table and a CSV file for data
SQL Server Integration Services Project Parameters
Project Parameters are scoped at the project level and can be used across several packages. Here is an example of using a Project Parameter.
- Imagine that you have several SSIS packages in a project and you want to configure the same values in all the packages at the same time. With variables, your scope is the package, however with Project Parameters you can use the same parameter in all the packages. In this example, we will show how to read values from a project parameter. We will import a specified number of rows from a CSV file to a SQL Server table.
- In the Solution Explorer, double click on Project.params:
- You can create parameters in the same way that you create a variable. It requires a Name, a Data Type and a Value. The Sensitive property is used to store sensitive data. When this value is set, the data is encrypted. The Required property is used when the value is required before executing the package. Finally, you can add a description for the parameter. We will set the value to 70:
- In the packages we will copy a specified number of rows from a CSV file. We will use the Bulk Insert Task to copy data from a CSV file to an Excel file.
- Double click on the Bulk Insert Task and create a connection to a SQL Server table as a destination table. In the column delimiter, select comma. In the file, select a CSV file with the data to import to the SQL Server table:
- In the Expressions page, click the browse button:
- Select the LastRow property and in Expressions click the browse button:
- Expand the Variables and Parameters Tree and drag and drop the Parameter created in step 3:
- Once you accept the expression, you will receive an error message that say "Cannot convert System.Int32 to System.Int64":
- To convert to System.Int64, expand the Type Casts folder and drag and drop DT_I8. What we did is specify the number of rows to be equal to the project parameter named NumberOfRows. We specified the lastrow property to the value of 70 that was set above.
- Run the package:
- If you want you can check the number of rows in the SQL Server destination table:
- As you can see, 70 rows were imported (the value specified in step 3). We imported 70 rows from the CSV file with the value read from the project parameter.
SQL Server Integration Services Package Parameters
Package Parameters are scoped at the package level. Here is an example of using a Package Parameter.
- In this new example, we will create a parameter with a T-SQL command and pass the parameter value to the Execute SQL Task. To do this in an SSIS package, go to the Parameters page and create a new package. We will create a package parameter named query. The Data Type will be string and the value will be a T-SQL Backup command:
- Drag and drop the Execute SQL Task.
- Double click the Execute SQL Task and add a connection to SQL Server. In the SQLSourceType, select Variable. In the SourceVariable, select the query package parameter.
- Run the package.
- If everything is OK, a backup will be created. What we did is pass the parameter value created in step 1 as the command.
In this new tip, we learned how to use the Project Parameters and the Package Parameters. The Project Parameter can be used in multiple packages, where as the Package Parameter only works at the package scope. Project Parameters are replacing configuration files used in the past to store global configuration values. The parameters do not support expressions as values for variables. The parameter values are read-only during the execution of the package, they cannot be changed dynamically.
Here are several links that may be useful to you:
- Integration Services Parameters
- Set Parameter Values After the Project Is Deployed
- Review all SQL Server Integration Services tips
About the author
View all my tips