Scripted ODBC data source only returns one record in SSIS

Question

Post reply

Scripted ODBC data source only returns one record in SSIS

mjaramillo-794467

SSC Enthusiast

Points: 143
More actions
April 10, 2009 at 3:03 pm

#129295

I am scripting a data source using an ODBC connection (long story but it has to be an ODBC connection) to an AS400. I am using an ODBC datareader and the buffer addrow method and think I have everything coded correctly but can only get it to return one record. I know there are around 1300 records. The code for the CreaetNewOutputRows goes like this:
OReader = Ocmd.ExecuteReader
Do While OReader.Read
With PointOutputDestBuffer
.AddRow()
.ENTERDATE1 = OReader.GetInt32(0)
.SYMBOL = OReader.GetValue(1).ToString()
.POLICY0NUM = OReader.GetValue(2).ToString()
.MODULE = OReader.GetValue(3).ToString()
.FILLR1 = OReader.GetValue(4).ToString()
.RPT0AGT0NR = OReader.GetValue(5).ToString()
.FILLR2 = OReader.GetValue(6).ToString()
.REASMNDCD = OReader.GetValue(7).ToString()
.REASAMNDDS = OReader.GetValue(8).ToString()
.CANCELDATE = OReader.GetValue(9).ToString()
.EFF0YR = OReader.GetValue(10).ToString()
.EFF0MO = OReader.GetValue(11).ToString()
.EFF0DA = OReader.GetValue(12).ToString()
.EXP0YR = OReader.GetValue(13).ToString()
.EXP0MO = OReader.GetValue(14).ToString()
.EXP0DA = OReader.GetValue(15).ToString()
.TOT0AG0PRM = OReader.GetInt32(16)
End With
OReader.NextResult()
Loop
I only get the one record if I leave the OReader.NextResult() in the code even though I shouldn't have to use it otherwise it hangs up. Anyone have any ideas on how to retrieve all the data?

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

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

Tim Mitchell SSCoach Points: 15695 More actions · Answer 1

You should remove the line that reads:

OReader.NextResult()

You're actually duplicating the call your making with the reader.Read() at the top of the loop, which results in skipping rows within your data.

hth,

Tim

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

mjaramillo-794467 SSC Enthusiast Points: 143 More actions · Answer 2

I know but when I take that out, only the first Read seems to be done and then the process hangs and doesn't finish. Putting the nextresult in at least finishes it although only one record is written to the output (there are actually over 1300 records in the result)

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

mjaramillo (4/10/2009)
I am scripting a data source using an ODBC connection (long story but it has to be an ODBC connection) to an AS400. I am using an ODBC datareader and the buffer addrow method and think I have everything coded correctly but can only get it to return one record. I know there are around 1300 records. The code for the CreaetNewOutputRows goes like this:
OReader = Ocmd.ExecuteReader
Do While OReader.Read
With PointOutputDestBuffer
.AddRow()
.ENTERDATE1 = OReader.GetInt32(0)
.SYMBOL = OReader.GetValue(1).ToString()
.POLICY0NUM = OReader.GetValue(2).ToString()
.MODULE = OReader.GetValue(3).ToString()
.FILLR1 = OReader.GetValue(4).ToString()
.RPT0AGT0NR = OReader.GetValue(5).ToString()
.FILLR2 = OReader.GetValue(6).ToString()
.REASMNDCD = OReader.GetValue(7).ToString()
.REASAMNDDS = OReader.GetValue(8).ToString()
.CANCELDATE = OReader.GetValue(9).ToString()
.EFF0YR = OReader.GetValue(10).ToString()
.EFF0MO = OReader.GetValue(11).ToString()
.EFF0DA = OReader.GetValue(12).ToString()
.EXP0YR = OReader.GetValue(13).ToString()
.EXP0MO = OReader.GetValue(14).ToString()
.EXP0DA = OReader.GetValue(15).ToString()
.TOT0AG0PRM = OReader.GetInt32(16)
End With
OReader.NextResult()
Loop
I only get the one record if I leave the OReader.NextResult() in the code even though I shouldn't have to use it otherwise it hangs up. Anyone have any ideas on how to retrieve all the data?

Insert this line after you finish reading your source:

Call PointOutputDestBuffer.SetEndOfRowset()

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

mjaramillo-794467 SSC Enthusiast Points: 143 More actions · Answer 4

I figured it out - it's really something simple but I have never done a lot of VB.NET programming and this is my first scripted task. The executeReader command has to occur in the PreExecute subroutine and not in the CreateNewOutputRows sub.

Thanks for the help.