Sorted Input

DarrenSQLIS

If a component requires a sorted input it would seem reasonable that you can check the IsSorted property of the attached input, but this will always return false. I have tried this when connecting the output of the Sort transform to my component, and then check the IsSorted property for this input. It is always false. How can this be, and also how can I see if the path is indeed sorted

If using a virtual input column in my UI, I get a SortKeyPosition on the columns, but when overriding SetUSageType in the component class I always get zero for the key. Why is the sort information not quite there for me

Matt David

This is actually by design. The layout code will check to see if you are seeing the input as sorted and return that information to you instead of the actual values that are on the upstream columns. I think this is best seen with an example.

Say you have data from upstream sorted on ColumA (1) and ColumnB (2). If you mark that both columns are used (via SetUsageType) then you will get those SKP values and the IsSorted property will be true. However, if ColumnA is marked as ignored (not used) then the layout will see that you are not using the sorted column so to you the data is not sorted and, therefore, the layout will return that IsSorted is false. If, on the other hand, you had ColumnA as RO or RW (used) and ColumnB as ignored then you would get that IsSorted was true because you are looking at a column that does make the data seem sorted to you. This is also why the virtual input shows the SKPs. It is because all the columns are available so the data would be sorted while using those columns.

HTH,

Matt

Jamie Thomson

Matt David wrote:

This is actually by design. The layout code will check to see if you are seeing the input as sorted and return that information to you instead of the actual values that are on the upstream columns. I think this is best seen with an example. Say you have data from upstream sorted on ColumA (1) and ColumnB (2). If you mark that both columns are used (via SetUsageType) then you will get those SKP values and the IsSorted property will be true. However, if ColumnA is marked as ignored (not used) then the layout will see that you are not using the sorted column so to you the data is not sorted and, therefore, the layout will return that IsSorted is false. If, on the other hand, you had ColumnA as RO or RW (used) and ColumnB as ignored then you would get that IsSorted was true because you are looking at a column that does make the data seem sorted to you. This is also why the virtual input shows the SKPs. It is because all the columns are available so the data would be sorted while using those columns. HTH, Matt

Hi Matt,

Hopefully you're still getting Alerts on updates to this thread.

If I read what you said above correctly, I need to check the virtual input in order to know for sure that the input is sorted. Is that correct

Darren, how did you get round this problem I need to check that the input is sorted without the user having to select all the columns.

Regards

Jamie

Jamie Thomson

Forget it. I've answered my own question. Just check isSorted property of the virtual input.

Thanks

Jamie

DarrenSQLIS

You will also want to check the SortKeyPosition on the input columns selected. Depends what you are doing, but if they are out of sort order for you then, being IsSorted is not eneough, but of course it is a good start!

This is some code that has worked for me, the select in order limitation may be a bit much and you could work around that, but here you go all the same -

#region Validate Input Columns & Sort Key Sequence

if (!ComponentMetaData.AreInputColumnsValid)

return DTSValidationStatus.VS_NEEDSNEWMETADATA;

IDTSInputColumnCollection90 inputColumns = ComponentMetaData.InputCollection[0].InputColumnCollection;

int sortKeySum = 0;

foreach (IDTSInputColumn90 inputColumn in inputColumns)

{

if (inputColumn.SortKeyPosition > 0)

{

sortKeySum += inputColumn.SortKeyPosition;

}

else

{

PostError(String.Format("Column {0} is not part of the sort key. Columns selected must be seuqentially ordered within the sort key.", inputColumn.Name));

return DTSValidationStatus.VS_ISBROKEN;

}

}

if (SequentialSum(inputColumns.Count) != sortKeySum)

{

PostError(String.Format("Input columns are not sequentially sorted. Sequential sum check violation."));

return DTSValidationStatus.VS_ISBROKEN;

}

#endregion

Jamie Thomson

In my case, no columns are even being selected (its a fairly simplistic component) so I don't think its a problem - but thanks for the heads up.

-Jamie