SSIS(Script Component): Assign values to global variables & pass to stored procedure

Question

SSIS(Script Component): Assign values to global variables & pass to stored procedure

xparmanand

SSC Eights!

Points: 957
More actions
July 26, 2010 at 5:35 am

#139931

Hi Experts,
I have a SSIS package which was converted from dts which is using Global variables, now I want to assign integer value to global variable & pass to one stored procedure within a package!
I am importing data from test files to table Using a Dataflow task & using Script Component validating text file data & if it is wrong then I need to assign the some value to global variable & pass to stored procedure in next step.
I am using following code in script component but its giving error (The type of the value being assigned to variable differs from the current variable type. Variables may not change type during execution)
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim var As IDTSVariables90
If Trim(Row.Column3) IsNot Nothing And Trim(Row.Column3) <> "" And IsNumeric(Trim(Row.Column3)) Then
Row.PORTFOLIOPENETRATIONAMT = Row.Column3
Else
Me.VariableDispenser.LockOneForWrite("Error1", var)
var("Error1").Value = 2
var.Unlock()
End If
End
Please help!
Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Baskar B.V SSCrazy Points: 2823 More actions · Answer 1

Can you check the data type of the variable "Error1"? Also is there a need to perform this inside data pump? You can do this in control flow itself and then pass the variable to the Stored procedure. Are you calling the SP inside the data flow?

[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

xparmanand SSC Eights! Points: 957 More actions · Answer 2

Hi,

Thanks for reply, any way i got the solution

I defined a local variable in the script component's & assign the value to that variable inside process_row event & then write postExecute event & assign that varible to package variables.

Public Class ScriptMain

Inherits UserComponent

Dim var As IDTSVariables90

Dim temp1 As Integer = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

'

' Add your code here

'

'----1

If Year(CDate(Row.Column0)) > 2000 Then

Row.ASOFDATE = Row.Column0

Else

temp1 = 2

Exit Sub

End If

End

Public Overrides Sub postexecute()

Me.Variables.Error1 = CShort(temp1)

End

Thanks,

Phil Parkin SSC Guru Points: 246987 More actions · Answer 3

Looks like you are running that code for every row in your pipeline and then assigning a single result - presumably based on the values in the last row in the pipeline - to a variable. Why would you do this? And your original post mentioned using this in a stored proc - how and when are you running this stored proc?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Baskar B.V SSCrazy Points: 2823 More actions · Answer 4

As Phil mentioned this would be a flaw in the code as this event (post-execute) would get executed only for the final row in the pipeline.

[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]