Destination Script Component - multiple oledb.datareaders?

Question

Post reply

Destination Script Component - multiple oledb.datareaders?

John-79567

SSCommitted

Points: 1892
More actions
May 11, 2007 at 3:19 pm

#177956

Is it possible to have more than one oledb.datareader inside a single script component? Here's my situation - I am running records through a script component (destination). The script component takes the records and checks for existence in 2 tables. Then performs and update/insert based on existence.
Actual Code:
Public Overrides Sub MyInput_ProcessInputRow(ByVal Row As MyInputBuffer)
Dim AuditsReader As OleDbDataReader
oledbcmdCheckAudits.Parameters(
"@Busdate").Value = Row.BusDate
oledbcmdCheckAudits.Parameters(
"@Store").Value = Row.Store
AuditsReader = oledbcmdCheckAudits.ExecuteReader()
If AuditsReader.Read() Then
With oledbcmdUpdateAudit
.Parameters(
"@Busdate").Value = Row.BusDate
.Parameters(
"@Store").Value = Row.Store
.ExecuteNonQuery()
End With
Else
With oledbcmdInsertAudit
.Parameters(
"@Busdate").Value = Row.BusDate
.Parameters(
"@Store").Value = Row.Store
.ExecuteNonQuery()
End With
End If
AuditsReader.Close()

Dim AdjustmentReader As OleDbDataReader
oledbcmdCheckAdjustment.Parameters(
"@Busdate").Value = Row.BusDate
oledbcmdCheckAdjustment.Parameters(
"@Store").Value = Row.Store
AdjustmentReader = oledbcmdCheckAdjustment.ExecuteReader()
If AdjustmentReader.Read() Then
With oledbcmdUpdateAdjustment
.Parameters(
"@BusDate").Value = Row.BusDate
.Parameters(
"@Store").Value = Row.Store
.ExecuteNonQuery()
End With
Else
With OledbCmdInsertAdjustment
.Parameters(
"@BusDate").Value = Row.BusDate
.Parameters(
"@Store").Value = Row.Store
.ExecuteNonQuery()
End With
End If
AdjustmentReader.Close()

End Sub
Problem:
The problem i'm seeing is that the script component only works on the first record. If i remove the second reader then it works properly.
Thanks in advance,
SSISMakesMeSad

Viewing 3 posts - 1 through 2 (of 2 total)

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

bledu SSChampion Points: 12335 More actions · Answer 1

change your code in the second part

[PosactAReader = oledbcmdCheckAdjustment.ExecuteReader()

If AdjustmentReader.Read() Then]

to

[AdjustmentReader = oledbcmdCheckAdjustment.ExecuteReader()

If AdjustmentReader.Read() Then]

Everything you can imagine is real.

John-79567 SSCommitted Points: 1892 More actions · Answer 2

Thanks for taking a look. I made a mistake in my post when trying to change object names. I corrected the typo. It looks like i can only have 1 oledb data reader in the script component.