How to add variables to SSIS Control Flow tasks?

Problem: How to add variables to SSIS Control Flow tasks?

Solution:   Variables

Variables store values that a SQL Server Integration Services package and its containers, tasks, and event handlers can use at run time. The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions.

Use case of variables in SSIS:

      ·         Execute SQL Task inputs, outputs
·         For loop iterations
·         Filtering result sets based on input values in where clause to extract
In SSIS variables are passed to SQL as “?” place holder.

Example:

In the example, I would like to show we can pass a variable to execute sql task to update records based on a date using stored procedure.

Step 0: Create a variable input date as string and set the scope to package.

Step 1: From the tool box drag Execute SQL Task, set properties of the task and use the procedure and assign the variable using place holder.

Step 2: Set the parameter properties order as 0 and assign the length as -1.

Execute the task.

Output: