SSIS Script Component Write to Variable
This is with SQL 2005.
I have a script component inside a Data Flow Task. I would like to read from the input columns and write the data to a global user variable.
I've set my input columns and added my global user variable as a ReadWriteVariable to the script component properties.
Here is my code, I'm just trying to alter the value of the global user variable here, but its not working. When I write out the value of the variable in another task it still has its default value:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim updateSQL As String
Public Sub Main()
Dim vars As IDTSVariables90
VariableDispenser.LockOneForWrite("SQL_ATTR_Update", vars)
vars("SQL_ATTR_Update").Value = "Test"
vars.Unlock()
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'updateSQL = Row.ITMID + Row.PRCCAT
End Sub
End Class
I have also tried with no luck:
Me.ReadWriteVariables("SQL_ATTR开发者_JS百科_Update").Value = "Test"
I figured it out.
From MS:
In Script component code, you use typed accessor properties to access certain package features such as variables and connection managers.
The PreExecute method can access only read-only variables. The PostExecute method can access both read-only and read/write variables.
For more information about these methods, see Coding and Debugging the Script Component.
http://msdn.microsoft.com/en-us/library/ms136031.aspx
It looks like Dts is only available in Script Task.
Here is what the code looks like:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim updateSQL As String
Public Overrides Sub PostExecute()
Me.ReadWriteVariables("SQL_ATTR_Update").Value = "Test"
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'updateSQL = Row.ITMID + Row.PRCCAT
End Sub
End Class
Here is how you could do it: http://microsoft-ssis.blogspot.com/2011/01/how-to-use-variables-in-script.html
Use
Dts.Variables("SQL_ATTR_Update").Value = "Test"
And SQL_ATTR_Update needs to be a global variable and listed in your ReadWriteVariables for that script task.
精彩评论