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?
CodePudding user response：
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.
CodePudding user response：
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.