Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Updated: 2016-05-12   |   Comments   |   Related: More > 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


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


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


  6. Project parameters in SSIS


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


  8. Bulk insert Task


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


  10. Bulk insert properties


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


  12. Browse button to edit expressions


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


  14. SSIS Expression editor


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


  16. Project parameter expression


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


  18. Error message


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


  20. Converting the SSIS datatype


  21. Run the package:


  22. Executing the package

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


  24. The destination table


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


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


  3. Drag and drop the Execute SQL Task.


  4. Execute SQL Task


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


  6. Execute SQL Task properties


  7. Run the package.


  8. Execute SQL Task


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


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



Last Updated: 2016-05-12


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools