SSIS Custom Component: Having any IsSorted Property and output metadata

Question

I have a Custom Synchronous Component that works fine and I use it.
Recently, I sent some Sorted data from a sort component to it (or an IsSorted=true Source Component)
but then i couldn't use the output as the input of a merge join due to not having a IsSorted=true property.

So I have to sort data again and it reduces the package performance too much.

Also I can't have any metadata same as Input, for my output(s) during design time.

I guess when my component is synchronous so it might be sorted as its input if not, how to make the component output data sorted!
I really wanna know if there is any clever solution to solve this detailed issues about Custom Pipeline Components.

I don't quite understand but... it seems like you need to extend your Custom Synchronous Component to include the IsSorted property. But like I said in your other question, I find it a good idea to do as much processing in the database as possible. If you have a complex package with lots of components, you should probably learn a bit more about databases — Nick.McDermaid
@Nick.McDermaid actually our goal is to integrate a huge data. So SSIS gives a modular package So we can track data in detail. Also a better error handling in SSIS we have. It was easier to do all these in database at first then we change our mind when we learned ssis is such a better solution for what we do. — El.Hum

JotaBe JotaBe · Accepted Answer · 2018-05-08T11:12:13

As your component is synchronous, somewhere in your code you are synchronizing the output, IDTSOutput1xx, with the input, IDTSInput1xx, with code like this:

output.SynchronousInputID = input.ID;

In a synchronous component the PipelineBuffer (the one exposed to the output, exposed in ProcessInput) is based upon the input buffer (usually with modified or added columns) respecting the original row ordering.

So, if you check that the input is ordered, you can assure that the output is also ordered. And there is a property that you can use to read this information from the input and set it in the output:

output.IsOrdered = input.IsOrdered

Take into account that you could set this property to true even if the output was not ordered, but in this case, you're relying on the information provided from the input, which should be correct.

You should only change this property explicitly to true in an asynchronous component in which you really sort the rows before returning them. But, as I told, you could lie, and set this property to true without returning ordered rows. In other words, it's informative metadata.

If this doesn't work for you, you'll also have to set the SortKeyPosition of the required columns in output.OutputColumnCollection. This information is also used by Merge Join to ensure that the input apart from being ordered, is ordered by the required columns.

If you want to see how you can do this using the SSIS task editor, instead of doing it "automagically" in your custom component, please, read IsSorted properties in SSIS or SSIS #98 – IsSorted is true, but sorted on what?

SSIS Custom Component: Having any IsSorted Property and output metadata

2 Answers