Best approach to pass SSIS parameter/variable value

Question

There are multiple ways to pass values to an SSIS package. We could use variables, Package parameters, or Project parameters also we could save values in a table and ask SSIS to pick the values from the table. Now, my way of coding is to use project Parameters and get the variables to use them. When deployed to the SSIS catalog, ENV can be set up to overwrite Param values as user requirements. Now, Am weighing the risks/ease of setting up ENV by the user to pass param values vs setting up a table to save values and code in SSIS to pick the values. Pls, pour in your thoughts on the pros and cons of both these approaches.

For eg: let's assume we have an SSIS package to save data to a CSV file. And Folder path where CSV files must be saved varies depending on servers(DEV/UA/Prod). Is it best to save folder path value in a table along with server name or is it best to set folder Path value as Param and ask the user who executes to set up the folder value in ENV at the time of execution depending on the server?

Update on 23 Mar 2022 - Based on all valuable inputs, I decided to use parameters and variables rather than using SQL table to pick values.

Answer 1

There are several methods available and each have there pros and cons.

Utilizing parameters whether package or project level are good for anything that needs to change on a regular basis at execution. As those can be changed with a script task and then the package can be started at the end of the script. This also means whoever needs to execute the packages must have the appropriate security levels and knowledge.

Setting up environments is good for static content such as connection strings or email addresses for errors. It is possible to set up one master environment and have other folders utilize that environment or you would need to set it up for each folder. The downside is the person deploying the package needs to know how they are used and if they are outside the catalog folder than an extra step is required for mapping in the SQL server agent.

My preferred method is to create one table that holds the information and then the package connects to the table first step and loads the values to the variables. I have implemented this at my current position and it has become standard on all packages. It allows for some content to be defaulted and we have tables for DEV, QA, and Prod so as the packages are migrated the values are filled in. The table contains the Package Name, Variable Name, Variable Value, and audit columns to see when rows where added or updated. The table is temporal so it tracks all changes.

The packages execute a SP that pivots the rows to return a single row. The pivot is dynamic so it adds columns to the result set as needed. When a value is marked as default for the package name it appears for all packages but if the same Variable Name is listed under the package name it will show instead of the default. For example when testing I may want to send all error messages to my email instead of the group inbox. I will add a record of My Package Name, Email_Alert, My Email Address. This will then show as my email address for testing, when going to QA or Prod I do not include that record in the other tables so it uses the default inbox.

Utilizing the table also gives me the ability to have a SSRS report that shows the variables used for each package and also allows for me to change the values as needed while keeping an audit log of who changed what value and when. This is useful when something needs to change for backdating or anything else as I can make the change execute the package and then change the value back. If the department is ever audited on anything I have a full audit trail that I can provide in a matter of minutes and not days. There is also a rule that has been implemented that no values are allowed to be hard coded into variables anymore they must be in the table. Stored Procedure Names are also saved in the table and passed to the package so if we need to update a SP we do not need to redeploy the package.

We try to build all SSIS packages so we can adjust to changes without needing to redeploy as that is when mistakes often are made.

Answer 2

As mentioned in the official documentation:

Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution.

Parameters were introduced in SQL Server 2012. This option was added to avoid using variables and external configuration files to pass arguments on the package execution.

If the CSV files directory changes based on the package environment, this means that parameters are the best suited for this situation. Still, other options can be used.

References

• SSIS Parameters vs. Variables

Answer 3

In my experience variables are best served by using an Execute SQL task and returning the results to a variable. It's modular and means it certain steps can easily be disabled if need be.

For managing connections (without outright hard-coding a connection string) I'd advise saving the CSV file location via a parameter. A parameter can be modified in the deployment environment via your SQL Script Agent and doesn't require changes to the source table. If I can avoid it, I wouldn't ever put file location information in the source table as it makes the table less exportable.