I have a package variable that I set via an ExecuteSQL task. I want to reference it in a data flow script component. In the Script component I enter the variable into the ReadOnlyVariables collection, then in the script I reference it as Me.Variables.var. (E.G. counter = Me.Variables.var)
I'm getting errors when the data flow starts:
Error: 0xC0047062 at Provider, Set Surrogate Key [4261]: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Pipeline.ReadWriteVariablesNotAvailableException: The collection of variables locked for read and write access is not available outside of PostExecute.
I have no problem referencing other variables that I have in DerivedColumn transformations. I've tried putting the variable in the ReadWriteVariables collection but I get the same error. I don't understand why this is so difficult. Please help.
Using variables in data flow Script component
bl4ckprint
Jason Walsh
Gordon,
You can reference it in the PreExecute() routine. In there you can assign the value to a variable declared within the script task (e.g. dim myvar as String) and use it within your script task.
-Jamie
carywinton
Just underneath Public Class ScriptMain type the following:
"public overrides sub "
You will then get a list of methods that cna be overridden. One of them is PreExecute().
Look here in BOL: http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.scriptcomponent.preexecute.aspx
-Jamie
erd
Jamie,
I'm not sure what you mean by the PreExecute() routine. Here is my code:
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 counter As Integer
Public Sub New()
counter = Me.Variables.MaxProvId
End Sub
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
counter += 1
Row.ProvGFId = counter
End Sub
End Class