Need Example of Source Component with Multiple non-Error Outputs

Can someone please point me to one or more examples of a Source component that has two or more outputs?

I wrote a source, which works with a single output, but after adding a second output, I see no rows there. I've single-stepped the code in the debugger, and it looks like it should be adding rows to the second output, but the downstream component never sees any.

Thanks.

Synchronous or async?

Here's a synchronous one I wrote a while back:

http://agilebi.com/cs/blogs/jwelch/archive/2007/07/12/processing-a-flat-file-with-header-and-detail-rows.aspx

Here's a different one from Jamie:

http://blogs.conchango.com/jamiethomson/archive/2005/09/05/SSIS-Nugget_3A00_-Multiple-outputs-from-a-synchronous-script-transform.aspx

|||

A source will always be synchronous, by definition. This is a full source component, not a script.

I do not have a suitable sample I can give away, but I can describe it-

Use ProvideComponentProperties to add two outputs, setting ExternalMetadataColumnCollection.IsUsed to true. I also add a custom property as a marker to identify the purpose of each output later on.

Validate and RMD are much the same as usual, do the work that needs doing. PreExecute I use to get the ID of each output and store it in a suitable variable. Same concept as we do with the column index, and we do that too.

In PrimeOutput the array of output ID values can be compared with the ID values you cached in PreExecute. The index in the outputIDs array matches the index in the buffers array, so you can determine which buffer in that array matches which functional output.

Then just add data as normal, add rows and set column values. Do this to each buffer as required.

Finally be sure to call buffer.SetEndOfRowset() on all buffers in the buffers array.

Any help?

|||

Thanks, that's just about what I'm doing, though my (unusual) source doesn't have external metadata. Does it need to?

Like I said, I'm probably doing something silly. I think my next step is to abstract away the specifics of my source. I'll create a source that just returns 10 rows to one output and 10 rows to the other. If that works, then my silly mistake will be in how I send rows to the second output.

Thanks to all.

|||

You do not need it I guess, but it is usual. It acts as a pattern that can be used to describe any external dataset in SSIS terms. We normally work on a model where you can select one or more columns from the external data source, but it does not have o be all. The metadata acts as the choice and output columns are the selection. It also helps for maintenance and validation of columns, because of the separation layer. Still if you had a very stable closed source it may make sense not to bother. I tend to write more flexible components aimed at a wider market, rather than a specific project component.

You send rows to both outputs in exactly the same way, you get multiple buffers in the array, so just add the row to the correct one. That bit the same as havig one buffer, you just need to work out which buffer is which in the array and reference it.

Good luck.

|||

John Saunders wrote:

Like I said, I'm probably doing something silly.

In fact it was so silly, I didn't even realize it. It was bad test data due to having moved from one development environment to the next. I was chaging one file but testing with another file of the same name in a different directory. Very silly.

|||

DarrenSQLIS wrote:

A source will always be synchronous, by definition.

Err...are you sure? I kinda think a source is always asynchronous cos its creating a new execution tree.

-Jamie

sql