Variables are used in many different ways in Integration Services packages. You will probably find that package development does not progress far before you have to add a user-defined variable to your package to implement the flexibility and manageability your solution requires. Depending on the scenario, system variables are also commonly used.
Property Expressions Use variables to provide values in the property expressions that set the properties of packages and package objects. For example, the expression, SELECT * FROM @varTableName includes the variable varTableName that updates the SQL statement that an Execute SQL task runs. The expression, DATEPART("d", GETDATE()) == 1? @[User::varPackageFirst]:@[User::varPackageOther]", updates the package that the Execute Package task runs, by running the package specified in the varPackageFirst variable on the first day of the month and running the package specified in the varPackageOther variable on other days. For more information, see Using Property Expressions in Packages.
Data Flow Expressions Use variables to provide values in the expressions that the Derived Column and Conditional Split transformations use to populate columns, or to direct data rows to different transformation outputs. For example, the expression, @varSalutation + LastName, concatenates the value in the VarSalutation variable and the LastName column. The expression, Income < @HighIncome, directs data rows in which the value of the Income column is less than the value in the HighIncome variable to an output. For more information, see Derived Column Transformation, Conditional Split Transformation, and Using Expressions in Packages.
Precedence Constraint Expressions Provide values to use in precedence constraints to determine whether a constrained executable runs. The expressions can be used either together with an execution outcome (success, failure, completion), or instead of an execution outcome. For example, if the expression, @varMax > @varMin, evaluates to true, the executable runs. For more information, see Adding Expressions to Precedence Constraints.
Parameters and Return Codes Provide values to input parameters, or store the values of output parameters and return codes. You do this by mapping the variables to parameters and return values. For example, if you set the variable varProductId to 23 and run the SQL statement, SELECT * from Production.Product WHERE ProductID = ?, the query retrieves the product with a ProductID of 23. For more information, see Execute SQL Task.
For Loop Expressions Provide values to use in the initialization, evaluation, and assignment expressions of the For Loop. For example, if the variable varCount is 2 and varMaxCount is 10, the initialization expression is @varCount, the evaluation expression is @varCount < @varMaxCount, and the assignment expression is @varCount =@varCount +1, then the loop repeats 8 times. For more information, see For Loop Container.
Parent Package Variable Configurations Pass values from parent packages to child packages. Child packages can access variables in the parent package by using parent package variable configurations. For example, if the child package must use the same date as the parent package, the child package can define a parent package variable configuration that specifies a variable set by the GETDATE function in the parent package. For more information, see Execute Package Task and Package Configurations.
Script Task and Script Component Provide a list of read-only and read/write variable to the Script task or Script component, update the read/write variables within the script, and then use the updated values in or outside the script. For example, in the code, numberOfCars = CType(Dts.Variables("NumberOfCars").Value, Integer), the script variable numberOfCars is updated by the value in the variable, NumberOfCars. For more information, see Using Variables in the Script Task.