February 8, 2010 at 9:51 am
Hi all
I have a script task I have used to populate my variable as below.
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
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
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
If IsNumeric(Row.ISRCTN) Then
Row.IsNumeric = True
Else
Row.IsNumeric = False
End If
End Sub
Public Overrides Sub PostExecute()
Me.Variables.FailureReason = "Failed"
End Sub
End Class
Everthing I have says to do what I have done and declare and populate the variable in a PostExecute() method.
My question is now how to use the variable, I have tried using a derived column with the variable as the value and outputting that to an excel spreadsheet but I just get a blank column. I can see that the variable is populated when I use a PostExecute breakpoint, but I have not joined something up here.
I have to admit I don't really understand why this should be done as a post execute method or how to the use it so I could be going wrong there, please help!!
Thanks
February 9, 2010 at 5:35 pm
I have had luck with making sure the variable is read/write on the script object properties. Then in the later task, assign it using an expression.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 10, 2010 at 9:58 am
yes the variable was read\write as it wouldn't correctly save if it wasn't I noticed.
However I have found out that the postexecute mthos only executes after the data flow has completed so I can't use that variable in the same data flow. Also you can't assign it in the preexecute method either.
In the end I had to hardcode some variables which seems particularly crappy on SSIS's part.
February 10, 2010 at 12:38 pm
Look into using VariableDispenser - you will be able to update your variables in the pipeline of the Script Component if you use that, rather than the Read Write variables attributes you've been trying (the variables should be removed from there if you implement VariableDispenser).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 28, 2010 at 12:08 am
Can you use the variable dispenser in the Data Flow script component? (as apposed to Control Flow)
When I try to declare the following I am told the "Dts" name has not been defined. This works fine in the Control Flow Script Objects.
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply