Every now and then you need a variable in a script task. For instance to avoid a hardcoded path or connectionstring in your code. You can use Package Configuration to fill a variable and use that variable in your script task.
Solution
There are two ways to read and write variables in a Script task. I will show you both.
1) Create variables
Let's create to string variables called ReadVariable and WriteVariable.
Right mouse click in Control Flow |
2) Script task
Add a script task to your package and give it a suitable name.
The Script Task |
3a) Simple version
Add ReadVariable to the ReadOnlyVariables and add WriteVariable to the ReadWriteVariables:
Let the script know what variables you want to use. |
Now the code is only one row:
// C# code public void Main() { // Fill WriteVariable with value from ReadVariable Dts.Variables["User::WriteVariable"].Value = Dts.Variables["User::ReadVariable"].Value; Dts.TaskResult = (int)ScriptResults.Success; }
' VB.Net code Public Sub Main() ' Fill WriteVariable with value from ReadVariable Dts.Variables("User::WriteVariable").Value = Dts.Variables("User::ReadVariable").Value Dts.TaskResult = ScriptResults.Success End Sub
3b) Advanced Version
In the advanced version you don't add the variables to the list. We will do that in the code:
// C# code public void Main() { // Lock variables Dts.VariableDispenser.LockForRead("User::ReadVariable"); Dts.VariableDispenser.LockForWrite("User::WriteVariable"); // Create a variables 'container' to store variables Variables vars = null; // Add variables from the VariableDispenser to the variables 'container' Dts.VariableDispenser.GetVariables(ref vars); // Now you can use the variables vars["User::WriteVariable"].Value = vars["User::ReadVariable"].Value; // Release the locks vars.Unlock(); Dts.TaskResult = (int)ScriptResults.Success; }
' VB.Net code Public Sub Main() ' Lock variables Dts.VariableDispenser.LockForRead("User::ReadVariable") Dts.VariableDispenser.LockForWrite("User::WriteVariable") ' Create a variables 'container' to store variables Dim vars As Variables = Nothing ' Add variables from the VariableDispenser to the variables 'container' Dts.VariableDispenser.GetVariables(vars) ' Now you can use the variables vars("User::WriteVariable").Value = vars("User::ReadVariable").Value ' Release the locks vars.Unlock() Dts.TaskResult = ScriptResults.Success End SubYou can even add a Try Catch Finally construnction to avoid unexpected errors (variable doesn't exists or is already locked). And with the finally you can release the locks even if your script fails, so you can still use the variable in for example an event handler.
// C# code public void Main() { // Create a variables 'container' to store variables Variables vars = null; try { // Lock variables Dts.VariableDispenser.LockForRead("User::ReadVariable"); Dts.VariableDispenser.LockForWrite("User::WriteVariable"); // Add variables from the VariableDispenser to the variables 'container' Dts.VariableDispenser.GetVariables(ref vars); // Now you can use the variables vars["User::WriteVariable"].Value = vars["User::ReadVariable"].Value; } catch (Exception ex) { // Throw an exception or add some logging throw ex; } finally { // Release the locks (even if your script task fails) vars.Unlock(); } Dts.TaskResult = (int)ScriptResults.Success; }
' VB.Net code Public Sub Main() ' Create a variables 'container' to store variables Dim vars As Variables = Nothing Try ' Lock variables Dts.VariableDispenser.LockForRead("User::ReadVariable") Dts.VariableDispenser.LockForWrite("User::WriteVariable") ' Add variables from the VariableDispenser to the variables 'container' Dts.VariableDispenser.GetVariables(vars) ' Now you can use the variables vars("User::WriteVariable").Value = vars("User::ReadVariable").Value Catch ex As Exception ' Throw an exception or add some logging Throw ex Finally ' Release the locks (even if your script task fails) vars.Unlock() End Try Dts.TaskResult = ScriptResults.Success End Sub
Which version is best? The result of both version is the same. The advanced version gives you a little more control over what is happening and at what stage the variables are locked and released, but the simple method is used more often in blogs and forums. I recommend using the simple method.
Note: Variables in a Script Component will be discussed at a later time.
Update: I wrote an SSIS WIKI about this where I also show how to use parameters in a Script Task
Thanks for the info, great help!!!
ReplyDeleteExcellent, thank you :-)
ReplyDeleteError:
ReplyDeleteen Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadWriteVariables()
en SC_a692aa72c9074d25a8f50e490e2fef5d.vbproj.Variables.set_FInicial(DateTime Value)
en SC_a692aa72c9074d25a8f50e490e2fef5d.vbproj.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
en SC_a692aa72c9074d25a8f50e490e2fef5d.vbproj.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
en SC_a692aa72c9074d25a8f50e490e2fef5d.vbproj.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)
en Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
Can't help you with this. It isn't the actual error message. Best option is to ask a question in this forum and give much more details (like code, screenshot, etc.).
DeleteYou could debug your script task or add a try catch with some logging to find the error.
Mine just hangs. Doesn't do anything . . .
ReplyDeleteIt's probably locked (by an other task). Please post the code and a description/screenshot of your package in the in the MSDN SSIS Forum.
DeleteThanks, I went successfully with your steps.
ReplyDeleteHi,
ReplyDeleteThis is the first time I am on this blog....
I have used the same logic to count the row based on the condition here is the code below. I am not able to access the variables after the update the variables in the script component. Please help.... I am using the variable in the next component to update the column. which is updating as 0.
=======================================================
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim X As Integer = 0
Dim Y As Integer = 0
Public Overrides Sub PostExecute()
MyBase.PostExecute()
'Me.Variables.XX= X
'Me.Variables.YY= Y
Me.ReadWriteVariables("XX").Value = X
Me.ReadWriteVariables("YY").Value = Y
MessageBox.Show(Me.Variables.XX.ToString)
MessageBox.Show(Me.Variables.YY.ToString)
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If (Row.Location = "A") Then
X= X+ 1
ElseIf (Row.Location = "B") Then
Y= Y+ 1
End If
End Sub
End Class
=============================================
Thank you,
You can only access variables that are changed by the Script Component after the Data Flow Task is completely finished (in the next task or in a precedence constraint going to the next task).
Deletereally helped
ReplyDelete