What is the difference between SSIS parameters and variables

KofiJun 22, 2020 | edited Jun 24, 2020 - by @sqldibia

Is there a difference between Package/Project Level Parameters  vs  Package/Project Level Variables in SSIS?

Emeka Okeke Author Jun 24, 2020

First of all, there is no such thing as SSIS project-level variables. Note that SSIS is an extensible ETL program, meaning that SSIS itself is an application with bunch of codes in it.

So whenever you are developing an SSIS package, think of it as though you are writing a code. The only difference is that your are doing it most of the time through the SSIS GUI, instead of writing the actual code. But those who are proficient with SSIS, ought to know .NET framework and SQL.

So with that said, your question should be what is the difference between Parameters and Variables in programming?

In programming lingo, parameters are variables that are passed as input values to functions, methods, procedures, or routines/subroutines. While variables are simply localized to the specific method or function in the program. In other words variables are defined and set within the body of the routine/function, whereas parameter is declared and its value set by the function/routine caller. A good example is TSQL stored procedure below:

CREATE PROC dbo.Sample_Proc
@In_Param INT,--Input Parameter
@Out_Param INT OUT--Output Parameter

    DECLARE @Local_Variable INT=1--Variable
    SET @In_Param+=2
    SET @Out_Param=@In_Param + @Local_Variable


--Calling dbo.Sample_Proc stored procedure

EXEC dbo.Sample_Proc @In_Param=1,@out=@Out_Param

--Value of @out variable will now be 4

When you call this Sp, the value of the @out variable will be 4. Notice that the value of the parameter @In_Param is set by the caller and not by the SP that is being called. Also notice that the parameter's value was reset in the body of the SP by adding 2 to the original caller's value of 1, this shows that parameters just like vairables are also variables whose values can vary at any point in the program.

Now back to SSIS. The only difference between SSIS paramaters and a typical routine/procedure's paramater, is that SSIS parameters are readonly, that is you can not write to it, you can only read from it.

If you want to confirm this, create both a sample project paramater and package parameter, then drag and drop SSIS script task or script component and double-click on it to open the editor. Then under readwrite variables, try to add variables. You will notice that your package and project parameters will not be available for selection in the variable list. However if you try to add them under readonly variables, they will be available for selection.

You might also ask what is the difference between SSIS package parameters and project parameters?

Both type of parameters behave the same way as I stated so far, the difference between the two is the scope. First of all SSIS parameters are designed to pass package-level and project-level configuration values during package runtime.

So if you want a configuration value available only to a specific package, then you would use package parameter, on the other hand, if you want a configuration value - typically connection strings and folder paths - available to all the packages in your project, then you would use project parameters.

This is also one of the reasons we have project deployment model and package deployment model in SSIS. SSIS project parameter was introduced in SQL Server 2012 along with SSISDB catalog.

Kofi+ Follow
joinedDec 28, 2019

More from @sqljunkyking