SSIS Asynchronous Transformation keep the previous column value and set to the next line

Question

Just encounter an issue when using Asynchronous Transformation in SSIS. For simplifier the question, lets say I just have two outputs, "Name" and "Id".

My C# code is:

string name = "";
string Id = "";

if(...)   // read the first line to get Name and partial value of "Id"
{
Output0Buffer.AddRow();

Output0Buffer.Name = Row.Name.Trim();

Id = Row.Id.Trim();

}

else if(...)   //read the 2nd line to get the rest part of "Id"
{
  Output0Buffer.Id = Id + Row.Id.Trim();
}

So ideally, when two outputs fill the Output0Buffer, "Name" and "Id" will be output, but the issue is, when the 2nd line was read into the buffer, it initialize the String Id again, so the concatenation Output0Buffer.Id = Id + Row.Id.Trim(); does not work. (it works just like "" + rest part of the Id because the 1st part get initialized and 2nd part is the current value)

I would like to stick to use script component transformation at this moment, is there any ways to solve this?

I have not used the output buffer but have used values in a loop for what you are trying to do. I had a variable holding the data, passed the variable to the Script task, then concatenated the variable value (from previous loop) to the value I obtained from my script task, and then returned the updated/concatenated value back to SSIS variable and then it was used in the next loop. I would think the buffer would be similar where you have to pass (or read) the existing value in the buffer first. — Brad, Apr 19 '18 at 17:00

score 1 · Accepted Answer · answered Apr 19 '18 at 17:07

@Brad is on the right train of thought but you'll need to move the declaration of Id to a class level member instead of within the ... SendOutputBuffer or whatever it's called.

If your if statement, you'll then need to test whether Name matches the class level member Name.

You'll need to ensure you have read/write access to the OutputBuffer0 columns. I can't recall what the default is on an async component - might be just write.

Big picture, you're concatenting all the values (ID) into one big string and associating it to the key (name), yeah?

Oh and for performance, you might be better served by declaring Id as a StringBuilder and only casting to String when you assign it to the output buffer

Thank you! @ billinkc, I am testing it! – LONG Apr 19 '18 at 17:10 — LONG, Apr 19 '18 at 17:10

SSIS Asynchronous Transformation keep the previous column value and set to the next line

1 Answers1