Asynchronous Script Component

Hi--done some searching, but I am not finding exactly what I need. I am using an asynchronous script component as a lookup since my table I am looking up on requires an ODBC connection. Here is what my data looks like:



From an Excel connection:

Order Number

123

234

345



The table I want to do a lookup on has multiple rows for each order number, as well as a lot of rows that aren't in my first table:

Order Number Description

123 Upgrade to System

123 Freight

123 Spare Parts

234 Upgrade to System

234 Freight

234 Spare Parts

778 Another thing

889 Yet more stuff



etc. My desired result would be to pull all the items from table two that match on Order Number from table one. My actual results from the script I have is a single (random) row from table two for each item in table one.....So my current results look like:

Order Number Description

123 Freight

234 Freight

345 Null



And I want:

Order Number Description

123 Upgrade to System

123 Freight

123 Spare Parts

234 Upgrade to System

234 Freight

234 Spare Parts

345 Null



etc.... Here is my code, courtesy of half a dozen samples found here and elsewhere...




Code Snippet

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.Odbc


Public Class ScriptMain
Inherits UserComponent

Dim connMgr As IDTSConnectionManager90
Dim odbcConn As OdbcConnection
Dim odbcCmd As OdbcCommand
Dim odbcParam As OdbcParameter


Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.JDEConnection
odbcConn = CType(connMgr.AcquireConnection(Nothing), OdbcConnection)

End Sub



Public Overrides Sub PreExecute()

odbcCmd = New OdbcCommand("SELECT F4211.SDDSC1, F4211.SDDOCO FROM DB.F4211 F4211 Where F4201.SHDOCO = ?", odbcConn)

odbcParam = New OdbcParameter("1", OdbcType.Int)
odbcCmd.Parameters.Add(odbcParam)


End Sub


Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim reader As Odbc.OdbcDataReader
odbcCmd.Parameters("1").Value = Row.SO
odbcCmd.ExecuteNonQuery()
reader = odbcCmd.ExecuteReader()
If reader.Read() Then

With Output0Buffer
.AddRow()
.SDDSC1 = reader("SDDSC1").ToString
.SONumb = Row.SO
.SOJDE = CDec(reader("SDDOCO"))
End With


End If

reader.Close()

End Sub

Public Overrides Sub ReleaseConnections()
connMgr.ReleaseConnection(odbcConn)
End Sub


End Class



I just don't know what I need to do to get every row from F4211 where SDDOCO matches Row.SO instead of a single row...... Any ideas or help? Oh, the reason I am starting with my Excel connection is that sheet lists the Orders I need detailed data for, and is only a few hundred rows....F4211 is really really really big.



I have also worked out an alternate way to do this using merge join tasks...but then my datareader source goes off and fetches 300,000 rows from F4211 before my final result set of about 1200 rows. That just feels like a bad approach to me...or am I being over-cautious? I'm a newb (if you couldn't already tell)...so guidence is appreciated.



Thank you....