none
Controlling script component input buffer RRS feed

  • Question

  • Hi,

    Is there a way to control how the data is flowing into the script component's input buffer? I have sorted and grouped data coming into the script component and I'd need to be able to execute the code based on the groups. The problem is that the buffer reads the rows in kind of randomly. It may cut the buffer in the middle of the group which causes problems.

    Tuesday, December 27, 2011 12:42 PM

Answers

All replies

  • Hi,

    Is there a way to control how the data is flowing into the script component's input buffer? I have sorted and grouped data coming into the script component and I'd need to be able to execute the code based on the groups. The problem is that the buffer reads the rows in kind of randomly. It may cut the buffer in the middle of the group which causes problems.

    The component input reads rows in the order that they arrive. Note that all rows from upstream do not arrive all at once (i.e. they don't arrive in the same buffer), they arrive in multiple buffers and yes, some rows from the same "group" may appear in different buffers. This is not random and is working as it it supposed to.

    If you explain more about the "problems" that you are experiencing then folks here may be able to help.


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Tuesday, December 27, 2011 1:02 PM
    Moderator
  • Simplified, the problem is that I need to go through a group and set a couple of date columns, StartDate and EndDate for each row in the group. StartDate may be set to the value in the first row in the group and EndDate may be set to the value in the last row in the group. This is determined runtime. So this is the reason I don't want the groups to be split into different buffers.

    I can probably make a workaround by carrying the last rows in the buffer that belong to a group to the next buffer using a variable and handling things there, but it would be much simpler if the buffers could be controlled.

    Tuesday, December 27, 2011 1:18 PM
  •  

    I can probably make a workaround by carrying the last rows in the buffer that belong to a group to the next buffer using a variable and handling things there, but it would be much simpler if the buffers could be controlled.

    That sounds like the correct way to do it using a Script Component, not a workaround.


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Tuesday, December 27, 2011 8:48 PM
    Moderator
  • Ok, did that but there's a problem. When carrying the last group over to the next input buffer, the very last group won't be written to the output buffer because there's no new input buffer to carry it to. I tried to write the last group to the output buffer in PostExecute but the output buffer object is no longer available then so I get an error.

    Is there a way to find out which one is the last input buffer and then handle it accordingly, or do I need to add some sort of dummy group which can be dropped anyway to the end of my data?


    • Edited by Erba Wednesday, December 28, 2011 12:05 PM
    Wednesday, December 28, 2011 12:00 PM
  • Ok, did that but there's a problem. When carrying the last group over to the next input buffer, the very last group won't be written to the output buffer because there's no new input buffer to carry it to. I tried to write the last group to the output buffer in PostExecute but the output buffer object is no longer available then so I get an error.

    Is there a way to find out which one is the last input buffer and the handle it accordingly, or do I need to add some sort of dummy group which can be dropped anyway to the end of my data?


    Hello Erba,

    This is a shot in the dark but is it possible that are you running into the problem talked about here: http://blogs.msdn.com/b/michen/archive/2007/08/31/buffer.endofrowset.aspx

    Regards
    Jamie


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    • Marked as answer by Erba Wednesday, December 28, 2011 12:59 PM
    Wednesday, December 28, 2011 12:05 PM
    Moderator
  • Thanks Jamie. That link clarified the use of EndOfRowset.

    However, for some reason the EndOfRowset method doesn't recognize the last buffer. It doesn't matter where I put it.

     

    if (Row.EndOfRowset())
    	lastBuffer = true; // Scoped within the ScriptMain class
    do
    {
    	stuff();
    	// Doesn't work here either.
    }
    while (Row.NextRow());
    // Or here.
    if (lastBuffer)
    {
    	CreateNewOutputRows();
    	Output0Buffer.SetEndOfRowset();
    }
    else
    	CreateNewOutputRows();
    

    Could asynchronous input and output be the cause for EndOfRowset not working properly?

    • Edited by Erba Wednesday, December 28, 2011 12:47 PM Added question at the end
    Wednesday, December 28, 2011 12:42 PM
  • Ahh, found the answer. EndOfRowset doesn't work in overridden Input0_ProcessInputRow method. Input0_ProcessInput must be overridden and EndOfRowset used there: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/5ee18d02-c91c-41ed-80fe-e3f9040b8af9/


    • Edited by Erba Wednesday, December 28, 2011 12:53 PM
    Wednesday, December 28, 2011 12:53 PM