Variables

You can declare variables for your processing in SSIS.


1. Right Click on the designer page and select "Variables".

2. It displays a left pane for variables.

3. Enter a appropriate name, Datatype, and default value if any for the Variable.

4. It default restricts the variable to the package domain.This is the parent container of all other containers. However, you can also declare a variable restricted to some child container (e.g Sequence container, for Loop etc). You just need to select the required container on the designer surface and then click on insert new variable. In that case, it takes up the container as the domain for that varaible. The variable will exist only in that container.

5. You can assign a default value to a Variable.

6. A variable can also be configured to recieve its value at run time.



Setting a variable value at Runtime:


1] A variable value can be set using a formula.

2] This can be done by setting the Expression property in Properties window of the variable.

3] Click the variable in the variable pane and see the Properties window
OR
Open the Package Explorer tab ( seen in line with Control Flow,Data Flow tabs) and select the required variable and right click, select Properties.

4] Set EvaluateVaraibleAs Expression property as True.

5]Click on Expression value box, and click on the Ellipsis that appears there.


6] Drag and drop variable needed from the Variables pane. You can use the various operators, String functions etc from the other pane to perform a variety of operations.
7] A variable value however is calculated freshly every time it is accessed. So its value may differ depending on the value of the variables involved at that time.

Some examples commonly needed:
1] To get datetime in a particular format you can use a expression string like :
DT_STR,4,1252)YEAR( getdate() ) + RIGHT( "0"+ (DT_STR,4,1252)MONTH( getdate()) ,2) + RIGHT( "0"+ (DT_STR,4,1252)DAY( getdate() ) ,2) + RIGHT( "0"+ (DT_STR,4,1252) DATEPART( "hh", getdate() ) ,2) + RIGHT( "0"+ (DT_STR,4,1252) DATEPART( "mi", getdate() ),2) + RIGHT( "0"+ (DT_STR,4,1252) DATEPART( "ss", getdate() ),2)
This string will give you a datetime string evaluating to 20090121175730 (yyyymmddhhmmss)

2] If you need a datetime string to get evaluated to same value always, replace the "getdate()" function here with the package Start Time(@[System::StartTime]). It is a Package variable and set automatically at the start of execution and doesnt get modified with time.
(DT_STR,4,1252)YEAR( getdate() ) + RIGHT( "0"+ (DT_STR,4,1252)MONTH( getdate()) ,2) + RIGHT( "0"+ (DT_STR,4,1252)DAY( getdate() ) ,2) + RIGHT( "0"+ (DT_STR,4,1252) DATEPART( "hh", getdate() ) ,2) + RIGHT( "0"+ (DT_STR,4,1252) DATEPART( "mi", getdate() ),2) + RIGHT( "0"+ (DT_STR,4,1252) DATEPART( "ss", getdate() ),2)