A script component receives some input. But I just can't get at the first row?
Basically, if i use the NextRow method in the in the Do statement, then it advances the row collection to the second row before it gets into the code inside the loop? BUT, if I use the EndOfRowset property to define my loop then I get an error:
[PipelineBuffer has encountered an invalid row index value]
I'm guessing this means...I have to call NextRow before i access the data in the collection? But thats retarted because then I miss the first row? what? What am I missing?
This is the code which works but I miss the first row:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim strConcept As String
Do While Row.NextRow()
strConcept = Row.concept
updateDb(strConcept)
Loop
End Sub
This is the code which throws the invalid row index error:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim strConcept As String
Do While Not Row.EndOfRowSet()
strConcept = Row.concept
updateDb(strConcept)
Row.NextRow()
Loop
End Sub
I've put some try catches in there an the error happens on the line which calls Row.concept....?
Can anyone help, it must be something I'm messing up
thanks!!
andy
You can't advance to the next row because the next row is fed in by the data flow.
What are you trying to do?
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) is executed for every row passing through the script component.|||hey, thanks for the quick reply.
I have to say I don't really know about the internal workings of things...I'm new to SSIS, SQLServer and .NET...BUT, what I do know is that within that Sub I can in fact advance through the rows.....I do this like so:
Do While row.NextRow()
someVariable = Row.whatever_Input_Column_Ive_defined_in_the_components_input
If I use the above construct, then it does loop through the rows...NextRow() must return a False when there are no more rows and then the loop stops. Perfect...except I miss the first Row...? I'm assuming this is because NextRow is called before i even get into the loop....?
I must be doing something wrong....but what? Thanks!!
andy
|||But what are you trying to do? You don't need to call NextRow because the next row will be provided automatically. You can setup a variable outside of the sub and then inside the sub populate that variable as you see fit.|||oh, sorry Phil, I'm a bit confused.
I have a bunch of rows coming into this script component....in fact I have 6 rows. I just want to access each column of each row and within each iteration I want to do a SQL INSERT.
Now, I'm not entirely sure what you mean, but are you saying I don't need to even do I DO LOOP block?
What I do know is that I have tried to access each Row without calling NextRow but its throws that error:
[PipelineBuffer has encountered an invalid row index value]
?
Thanks...sorry if I'm not getting what you're saying...you might need to spell it out
much appreciated!
|||
andyrose wrote:
oh, sorry Phil, I'm a bit confused. I have a bunch of rows coming into this script component....in fact I have 6 rows. I just want to access each column of each row and within each iteration I want to do a SQL INSERT.
Why not just use an OLE DB Destination, instead of using a script, if this is what you want to do?|||ahhh...hold on, I think I get it...so SSIS calls that Input0_ProcessInpoutRow sub as many times as there are rows?
So it calls all the code in there...say...in my case, 6 times? But its thats kinda expensive....all this reduntant code...like connecting to a db is been called without need...?
am I right?
|||well...I lied a little. I kinda have to use a script just because within each interation I'm actually having to do some checks, which involve a load of other code...and only if I get a certain result does that row get added...so yeah, thats why I'm doing it in script
|||
ahhh...hold on, I think I get it...so SSIS calls that Input0_ProcessInpoutRow sub as many times as there are rows? So it calls all the code in there...say...in my case, 6 times? But its thats kinda expensive....all this reduntant code...like connecting to a db is been called without need...?
am I right?
Correct, and hence the question as to why you want to even use a script. SSIS comes with prebuilt destination components that will perform inserts for you.|||
well...I lied a little. I kinda have to use a script just because within each interation I'm actually having to do some checks, which involve a load of other code...and only if I get a certain result does that row get added...so yeah, thats why I'm doing it in script
And these checks can't be done with lookup components and/or derived columns?|||yeah maybe....truth is I've been thrown this SSIS thing without ever even knwoing this stuff existed...I've never used anything like it...
Its new to me, so I only know how to do stuff in code. I am learning, and SSIS is very cool, but I've got to get this done quickly and so do it I usually jump in the code...but yeah, I have used all the other components for simple stuff...its just when it has to do loads of checks here and there I just write the code and it gets done.
|||
Its new to me, so I only know how to do stuff in code. I am learning, and SSIS is very cool, but I've got to get this done quickly and so do it I usually jump in the code...but yeah, I have used all the other components for simple stuff...its just when it has to do loads of checks here and there I just write the code and it gets done.
Ah, okay, well, good luck! I hope I've helped you understand the script component. There's also a ProcessInput sub that actually passes each row to the ProcessInputRow sub. This is all in the books online though should you need a reference.|||
The script component tries to simplify things for you, so it builds the logic to loop through the rows in the buffer for you, and calls the ProcessInputRow method for you. If you want to see the code that's used, open your script component, click the design the script button to open VSA, and open the project explorer. The BufferWrapper code shows how they are putting friendly names for the column values, and the ComponentWrapper code shows how they are iterating the buffer. This probably goes without saying, but you shouldn't modify the code in either of these.
|||cool, thanks for your help phil....tried that but now am getting a validation VS_ISBROKEN error...though I guess thats whole other thread...thanks for your help!|||ah yeah, had a look, thanks jwelch. My experience is in classic ASP and VB so its weird to have .NET do everything for you...well not everything, but yeah, cool, thanks for the heads up!
