Variables
We can define variables in SSIS using variables window with specific scope. We can define variables at different scope levels and with different datatype, for example defining variable at package level, defining variable at tool level (specific to that particular tool for example variable at for loop level, variable at Execute SQL Task level etc.,)
Below are few examples to create variables
BIDS – View – Other Windows – Variables – It displays variables window and it consists the following icons
1. Add variable
2. Delete Variable
3. Show System Variables
4. Show All Variables
We can declare a variable with required data type and we can initialize value at the time of declaration itself.
To make use of variable we need to bind variable to tool, for example if you are working with script task below is the example.
Create two variables varInt (Int) = 123, varString (string) = “hello” at package scope level. When ever if you create a variable then it should be bind with any control, otherwise we can’t access that variable.
Example using Script Task
1. Drag the script task to control flow window
2. Right Click – Edit – Edit Script
Public Sub Main()
' Add your code here
MsgBox(Dts.Variables.Count) -- This code returns zero because no binding done
Dts.TaskResult = ScriptResults.Success
End Sub
To bind the variables to script task
1) Right click on script task – Edit
2) ReadOnlyVariable – Click on Ellipse and browse the variables which you want to use in the script.
3) Insert the below snippet to get the variable names
For i = 0 To Dts.Variables.Count - 1
MsgBox(Dts.Variables(i).Name.ToString())
Next
No comments: