Variables,Parameters and Expressions

SSIS includes multiple objects that can be used to create dynamic packages.SSIS can dynamically set a property in a task or component using an expression, which can be built using a set of building blocks, including variables,parameters,functions, literals, and more.

Variables

Variables are used to store values that SSIS executables can access at runtime.This object contains a value that can be hardcoded, dynamically set once, or modifi ed multiple times throughout the execution of the package. Principally, variables provide a method for objects in a package to communicate with each other.

You can use variables in SSIS in the following places:

• Setting up any connection string
• Setting up row count transform in the data flow task
• Used in For Each Loop, For Loop, Parameter mapping in Execute SQL Task.
• Script task

Parameters

Parameters are a new feature introduced in SQL Server 2012.SSIS uses two types of parameters: project parameters and package parameters. Project parameters are created at the project level and can be used in all packages that are included in that project.On the other hand, package parameters are created at the package level and can be used only in that package.

Package-level parameters are extremely useful for the parent-child package design. They allow you to pass specific values between the parent packages in order to configure the child packages.When using the project deployment model, parameters are the best choices to replace package confi gurations to create a dynamic and more flexible SSIS solution.

Expressions

Expressions are used in SSIS to return a value based on the evaluation of criteria. The expression
language itself consists of identifiers, literals, functions, and operators. Some expressions can be written to assign values to variables, return the value of a calculation, or be used as conditional statements. 

Variables and parameter difference

1.Execution
The value of a parameter can’t change within the execution of a package.That means, its value remains the same for the entire execution of the package.

The value of variables can change during the execution of the package

2.Scope
Parameters can be defined at 2 different levels:
Project. These type of parameters are available to all packages within the SSIS project. 
Package. These type of parameters are available only the package on which they were defined.

Variables can be scoped at the package, container, task or event handler level.