Using AddRow() in Output Buffer when C# transform in SSIS needs synchronous?

Question

First off I'm quite new to both SSIS and C#, so apologies for any rookie mistakes. I am trying to muddle my way through splitting one column by a specific delimiter from an input file that will have a variable length header, and a footer.

For example, Input0Buffer has one column. The actual data is always preceded by a row starting with the phrase "STARTDATA", and is bracketed with a row starting with "ENDDATA".

The one input column contains 5 bits of data separated by | . Two of these columns I don't care about.

Basically the input file looks like this:

junkrow

headerstuff

morejunk

STARTDATA

ID1|rubbish|stuff|apple|cheese

ID2|badger|junk|pear|yoghurt

So far I have tried to get some row-by-row logic going in the C# transformer, which I think I am happy with - but I can't work out how to get it to output my split data. Code is below.

bool passedSOD;
bool passedEOD;

public void ProcessRow(Input0Buffer data)      
{

string Col1, Col2, Col3;

if (data.Column0.StartsWith("ENDDATA")) 
{
passedEOD = true;
}

if (passedSOD && !passedEOD)
{
var SplitData = data.Column0.Split('|');

Col1 = SplitData[0];
Col2 = SplitData[3];
Col3 = SplitData[4];

//error about Output0Buffer not existing in context
Output0Buffer.Addrow();
Output0Buffer.prodid = Col1;
Output0Buffer.fruit = Col2;
Output0Buffer.dairy = Col3;
}

if (data.Column0.StartsWith("STARTDATA"))
{
passedSOD = true; 
} 
}

If I change the output to asynchronous it stops the error about Output0Buffer not existing in the current context, and it runs, but gives me 0 rows output - presumably because I need it to be synchronous to work through each row as I've set this up?

Any help much appreciated.

Answer 1

you can shorten your code by just checking if the row contains a '|'

if(Row.Column0.Contains("|")
{
     string[] cols = Row.Column0.Split('|');

     Output0Buffer.AddRow();
     Output0Buffer.prodid = cols[0];
     Output0Buffer.fruit = cols[3];
     Output0Buffer.dairy = cols[4];
}

Like Bill said. Make sure this is a transformation component and not a destination. Your options are source, transformation, and destination.

You also might want this as a different output as well. Otherwise, you will need to conditionally split out the "extra" rows.

Answer 2

Thanks both for for answering - it is a transformation, and thank you for the shorter way, however the header and footer are not well formatted and may contain junk characters also, so I daren't risk looking for | in rows. But I will definitely store that away for processing a better formatted file next time.

I got a reply outside this forum so I thought I should answer my own question in case any one else has a similar problem.

Note that:

• it's a transform

• the Output is be set to SynchronousInputID = None in the Inputs and Outputs section of the Script Transformation Editor

• my input is just called Input, and contains one column called RawData

• my output is called GenOutput, and has three columns

• although the input file only really has 5 fields, there is a trailing | at the end of each row so this counts as 6

Setting the synchronous to None means that Output0Buffer is now recognised in context.

The code that works for me is:

bool passedSOD;
bool passedEOD;

public override void_InputProcessInputRow(InputBuffer Row)
{
if (Row.RawData.Contains("ENDDATA"))
{
passedEOD = true;
GenOutputBuffer.SetEndOfRowset();
}

//IF WE HAVE NOT PASSED THE END OF DATA, BUT HAVE PASSED THE START OF DATA, SPLIT THE ROW
if (passedSOD && !passedEOD)
{
var SplitData = Row.RawData.Split('|');

//ONLY PROCESS IF THE ROW CONTAINS THE RIGHT NUMBER OF ELEMENTS I.E. EXPECTED NUMBER OF DELIMITERS
if (SplitData.Length == 6)
{
GenOutputBuffer.AddRow();
GenOutputBuffer.prodid = SplitData[0];
GenOutputBuffer.fruit = SplitData[3];
GenOutputBuffer.dairy = SplitData[4];
}

//SILENTLY DROPPING ROWS THAT DO NOT HAVE RIGHT NUMBER OF ELEMENTS FOR NOW - COULD IMPROVE THIS LATER
}

if (Row.RawData.Contains("STARTDATA"))
{
passedSOD = true;
}
}

Now I've just got to work out how to convert one of the other fields from string to decimal, but decimal null and allow it to output a null if someone has dumped "N.A" in that field :D