Getting Started with SQL Server Integration Services SSIS Parameters vs. Variables

By:   |   Updated: 2016-05-12   |   Comments   |   Related: > Integration Services Configuration Options


Problem

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?

Solution

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.

Requirements

  1. SQL Server Relational Database Engine installed
  2. SQL Server Integration Services installed
  3. SQL Server Data Tools Installed (SSDT) or BIDS (Business Intelligence Development Studio) installed
  4. You can use SQL Server 2012 or later versions. In this example, we are using SQL Server 2014
  5. 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.

  1. 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.


    2. File Sysem Task


  2. In the Solution Explorer, double click on Project.params:
    3. Solution Explorer parameters


  3. 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:


    4. Project parameters in SSIS


  4. 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.


    5. Bulk insert Task


  5. 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:


    6. Bulk insert properties


  6. In the Expressions page, click the browse button:


    7. Browse button to edit expressions


  7. Select the LastRow property and in Expressions click the browse button:


    8. SSIS Expression editor


  8. Expand the Variables and Parameters Tree and drag and drop the Parameter created in step 3:


    9. Project parameter expression


  9. Once you accept the expression, you will receive an error message that say "Cannot convert System.Int32 to System.Int64":


    10. Error message


  10. 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.


    11. Converting the SSIS datatype


  11. Run the package:


    12. Executing the package

  12. If you want you can check the number of rows in the SQL Server destination table:


    13. The destination table


  13. 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.


    14. Table displayed


SQL Server Integration Services Package Parameters

Package Parameters are scoped at the package level.  Here is an example of using a Package Parameter.

  1. 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:


    2. Parameters tab


  2. Drag and drop the Execute SQL Task.


    3. Execute SQL Task


  3. 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.


    4. Execute SQL Task properties


  4. Run the package.


    5. Execute SQL Task


  5. If everything is OK, a backup will be created. What we did is pass the parameter value created in step 1 as the command.


    6. Backup created


Conclusion

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.

Next Steps

Here are several links that may be useful to you:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2016-05-12

Comments For This Article

















get free sql tips
agree to terms