Script Component not recognizing when the last rowset is being processed

Question

Script Component not recognizing when the last rowset is being processed

william.hause

SSC-Addicted

Points: 462
More actions
April 16, 2009 at 6:32 am

#132829

I'm using the following code in the script component. The first two MsgBox's display during execution. The third MsgBox does not display. When the last record is processed and the MsgBox is displayed by the second MsgBox, Row.EndOfRowset is still False. According to everything I'vwe read it should be True. What have I not done? What do I need to do. I need to do some special processing after the last record have been processed.
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 i As Integer
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
i = i + 1
If i < 3 Then
[b]MsgBox("Provider.id = " & Row.PROVIDERID & vbCrLf & _
"Record number = " & i & vbCrLf & _
"Seq No = " & Row.EXP837SEQNO)
End If[/b]
If i > 118 Then
MsgBox("Provider.id = " & Row.PROVIDERID & vbCrLf & _
"Record number = " & i & vbCrLf & _
"Seq No = " & Row.EXP837SEQNO)End If
Me.Output0Buffer.AddRow()
Me.Output0Buffer.newFile = Row.PROVIDERID & Row.EXP837SEQNO
If Row.EndOfRowset Then
MsgBox("end of rowset encountered")
End If
'
End Sub
Public Overrides Sub CreateNewOutputRows()
'
' Add rows by calling AddRow method on member variable called "Buffer"
' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"
'
End Sub

Viewing 9 posts - 1 through 8 (of 8 total)

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

william.hause (4/16/2009)
I'm using the following code in the script component. The first two MsgBox's display during execution. The third MsgBox does not display. When the last record is processed and the MsgBox is displayed by the second MsgBox, Row.EndOfRowset is still False. According to everything I'vwe read it should be True. What have I not done? What do I need to do. I need to do some special processing after the last record have been processed.
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 i As Integer
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
i = i + 1
If i 118 Then
MsgBox("Provider.id = " & Row.PROVIDERID & vbCrLf & _
"Record number = " & i & vbCrLf & _
"Seq No = " & Row.EXP837SEQNO)End If
Me.Output0Buffer.AddRow()
Me.Output0Buffer.newFile = Row.PROVIDERID & Row.EXP837SEQNO
If Row.EndOfRowset Then
MsgBox("end of rowset encountered")
End If
'
End Sub
Public Overrides Sub CreateNewOutputRows()
'
' Add rows by calling AddRow method on member variable called "Buffer"
' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"
'
End Sub

Can you do your special processing in the PostExecute method?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

william.hause SSC-Addicted Points: 462 More actions · Answer 2

I have added an override to FinishOutputs() and created my additional two two records to the end of the file. Thanks

Public Overrides Sub FinishOutputs()

MsgBox("Creating final records")

Me.Output0Buffer.AddRow()

Me.Output0Buffer.newFile = "GE*1*78890~"

Me.Output0Buffer.AddRow()

Me.Output0Buffer.newFile = "IEA*1*000078904~"

End Sub

william.hause SSC-Addicted Points: 462 More actions · Answer 3

When I tried to use the PostExecute(), it did work. It only displayed info for Provider A, B & C. After processing the last Input record it did not execute the PostExecute () code and display Provider D.

Did I miss doing something?

Here is my data and the code

Data column 1 ProviderId, columns 2-3 Visits

A10

A07

A05

A19

B03

B16

B21

C05

D11

D12

D01

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 i As Integer = 0

Dim Prov As String

Dim TotalVisits As Integer = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

i += 1

If i = 1 Then

Prov = Row.ProviderId

End If

If Prov = Row.ProviderId Then

TotalVisits += Row.Visits

Else

MsgBox("Provider: " & Prov & vbCrLf & _

"Had " & TotalVisits & " visits")

TotalVisits = Row.Visits

Prov = Row.ProviderId

End If

End Sub

Public Overrides Sub PostExecute()

MsgBox("Provider: " & Prov & vbCrLf & _

"Had " & TotalVisits & " visits")

End Sub

End Class

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

william.hause (4/16/2009)
When I tried to use the PostExecute(), it did work. It only displayed info for Provider A, B & C. After processing the last Input record it did not execute the PostExecute () code and display Provider D.
Did I miss doing something?
Here is my data and the code
Data column 1 ProviderId, columns 2-3 Visits
A10
A07
A05
A19
B03
B16
B21
C05
D11
D12
D01
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 i As Integer = 0
Dim Prov As String
Dim TotalVisits As Integer = 0
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
i += 1
If i = 1 Then
Prov = Row.ProviderId
End If
If Prov = Row.ProviderId Then
TotalVisits += Row.Visits
Else
MsgBox("Provider: " & Prov & vbCrLf & _
"Had " & TotalVisits & " visits")
TotalVisits = Row.Visits
Prov = Row.ProviderId
End If
End Sub
Public Overrides Sub PostExecute()
MsgBox("Provider: " & Prov & vbCrLf & _
"Had " & TotalVisits & " visits")
End Sub
End Class

Your question is confusing. Did or didn't work? Can you please re-phrase or provide more information. From the way your component works it looks like transformational component.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

Also check following post, discussing EndOfRowset behavior.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

william.hause SSC-Addicted Points: 462 More actions · Answer 6

After a lot of research, I found the problem.

I left out the following line in the Public Overrides Sub PostExecture() Method

MyBase,PostExecute()

COZYROC One Orange Chip Points: 28499 More actions · Answer 7

william.hause (4/17/2009)
After a lot of research, I found the problem.
I left out the following line in the Public Overrides Sub PostExecture() Method
MyBase,PostExecute()

William,

The base method implementation is actually empty. So I don't think your problem was related to this line of code.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

john.arnott SSChampion Points: 11882 More actions · Answer 8

I posted this reply to same question in the General discussion section (just now found this thread, so edited original to direct interested parties here).

--------------------------------------------

I'm not claiming expertise here, but maybe my questions can help.

Why do you expect Row.EndOfRowset to be true? If the looping is outside the script and the script only gets run for each row in the resultset, it wouldn't get executed once the Row.EndOfRowset condition is reached, right?