Problem: In SSIS, you want to update a variable from a Script Component embedded in a Data Transform task. You get an error message reading “the collection of variables locked for read and write access is not available outside of postexecute”. What’s up?
Solution: Microsoft explains: “The collection of ReadWriteVariables is only available in the PostExecute.” OK, so what now? It turns out that this is really easy. In your script, add a class-level variable. Make sure you instantiate it. In the InputBuffer routine, update the class variable rather than the external package variable. Then override the PostExecute method; in your override you assign the class variable back to the package variable. Example:
Public Class ScriptMain
Inherits UserComponent
Dim msg As String = String.Empty
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If msg.Equals(String.Empty) Then
' This is the first row, so need to start the message with something
msg = "The following blah blah blah...."
msg += Environment.NewLine + Environment.NewLine
msg += "Number" + Environment.NewLine
msg += "------" + Environment.NewLine
End If
' Append new lines with each Row. "MyNumber" is the name of a column in a SQL query resultset
msg += Row.MyNumber.ToString().PadLeft(8, " "c)
msg += Environment.NewLine
End Sub
Public Overrides Sub PostExecute()
Variables.ReportMessage = msg.ToString()
MyBase.PostExecute()
End Sub
End Class
Posted with : Tech, Microsoft SQL Server and other databases, SQL Server, SQL Server Integration Service