Search code examples
ssissql-server-data-toolsdata-integrationcustom-pipeline-component

SSIS Custom Component: Having any IsSorted Property and output metadata

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.

Solution

  • 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?