none
where is the IsSorted property?

    Question

  • Hello. 

    within a data flow task I have two OLE DB sources that I am attempting to put into a merge join.

    When I connect them to the merge join transformation I get the following error:

    The IsSorted property must be set to true on both sources of this transformation.

    I understand what this means but I cannot see an IsSorted option in the properties of the OLE DB sources so I can't set it. Can you tell me where it is? for the time being I'm having to put a sort transform in to get it to work.

    Thanks

     

     

    Thursday, November 09, 2006 9:00 AM

Answers

  • I am assuming you are talking about a Merge join transformation, right?

    Merge join transformation requires to have both inptus sorted by the same columns you are using in the join. So, go back to the 2 source queires and make sure all columns used in the sort match across the 2 OLE DB source components.
    Rafael Salas | Don’t forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/
    • Proposed as answer by Raajakumari Friday, February 06, 2009 5:49 PM
    • Marked as answer by Bob BojanicOwner Tuesday, February 10, 2009 6:54 AM
    Friday, February 06, 2009 5:00 PM
    Moderator

All replies

  • Go to the advanced editor of the OLE DB source; then to the input and output properties. Expand the OLE DB source output node and the output columns folder. There is a property at the column label called 'sortKeyPosition.

     

    Rafael Salas

    Thursday, November 09, 2006 1:05 PM
    Moderator
  • The IsSorted property is on the Output, you then set the SortKeyPosition property on the columns within the output. You need both. Only set this if you actually have a sorted output, otherwise it will do nasty things.

    Read teh above in conjunction with Rafael's post though, this is all in the Advanced Editor.

    • Proposed as answer by Michael Nady Tuesday, September 08, 2009 9:42 PM
    Thursday, November 09, 2006 2:04 PM
    Moderator
  • Thanks Rafael and Darren.

    Even after I have set these two properties, it is still saying that the "IsSorted property is set to TRUE and Output Columns has Sort Key Position as ZERO" . This is not true. I am setting the property correctly and in order for all the columns. Any help?
    Raajakumari
    Friday, February 06, 2009 3:51 PM
  • Are you sure? I would double check that, and to be honest I have never seen SSIS falsely reporting that.


    Rafael Salas | Don’t forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/
    Friday, February 06, 2009 4:09 PM
    Moderator
  • Thanks a lot Rafael, I really appreciate you responding to a post in a 3 year old thread.

    Into the issue, I rechecked it. 

    It was my bad. I continued with the sorting numbers from first OLE DB Source to Second OLE DB Source. when I changed it, it went in fine.

    Now another issue.

    After that in the merge join, it is saying "Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata".

    What does this indicate? How to correct this?
    Raajakumari
    Friday, February 06, 2009 4:22 PM
  • I am assuming you are talking about a Merge join transformation, right?

    Merge join transformation requires to have both inptus sorted by the same columns you are using in the join. So, go back to the 2 source queires and make sure all columns used in the sort match across the 2 OLE DB source components.
    Rafael Salas | Don’t forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/
    • Proposed as answer by Raajakumari Friday, February 06, 2009 5:49 PM
    • Marked as answer by Bob BojanicOwner Tuesday, February 10, 2009 6:54 AM
    Friday, February 06, 2009 5:00 PM
    Moderator
  • Great! thanks a million Rafael,

    You are right on the dime. It worked.  I greatly appreciate your help here.

    Another related question,

    After the merge join, do I have to use another OLE DB Source or shall I take the output of the MergeJoin into my OLE DB Destination?

    My package is running successfully and reading the exact record, but the OLE DB destination is NOT INSERTING ANYTHING INTO THE TARGET DB.

    Any insight?

    Raajakumari
    Friday, February 06, 2009 6:54 PM
  •  

    If after the merge join you just need to insert the resulting rows, yes you should use an OLD DB Destination. As a best practice you set the destination to use fast load option and set the commit size and batch size to a number that is good for your server env. (hint: 10K is usually a good number)
    Rafael Salas | Don’t forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/
    Friday, February 06, 2009 6:58 PM
    Moderator
  • Thanks Rafael,

    It worked and inserted the record.

    Eventhough I selected left join in the dropdown on the connection, I had to select left join on the merge join as well. Initially, I did not do that.

    Also there was a wrong mapping which merge join took it by itself. Once it is removed. It worked fine.

    Please stay on the lookout for more questions from me. I have a creepishly designed application populating the data into an operational DB and my job is to migrate it to another DB.

    Hence facing all kinds of SSIS road blocks. Need lot of help.

    Will be back with more.

    Again thanks a million!!!!

    Raajakumari
    Friday, February 06, 2009 9:15 PM
  • Hi in Sortkey position their is zero value, what value i should fill in that it is not taking 1/true

    please help

    regards

    Sunil Yogann

    Wednesday, January 12, 2011 2:33 PM
  • Click on "OLE DB Source Output" in "Input and Output Properties" tab. Property is "IsSorted"
    -DishanF-
    Thursday, December 15, 2011 2:47 AM
  • Hi All,

    Below Link is Useful.

    http://msdn.microsoft.com/en-us/library/ms137653.aspx

    To manually set sort attributes on a source or transformation component

    1. In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.

    2. In Solution Explorer, double-click the package to open it.

    3. On the Data Flow tab, locate the appropriate source or upstream transformation, or drag it from the Toolbox to the design surface.

    4. Right-click the component and click Show Advanced Editor.

    5. Click the Input and Output Properties tab.

    6. Click <component name> Output, and set the IsSorted property to True.

      Note Note

      If you manually set the IsSorted property of the output to True and the data is not sorted, there might be missing data or bad data comparisons in the downstream Merge or Merge Join transformation when you run the package.

    7. Expand Output Columns.

    8. Click the column that you want to indicate is sorted and set its SortKeyPosition property to a nonzero integer value by following these guidelines:

      • The integer value must represent a numeric sequence, starting with 1 and incremented by 1.

      • A positive integer value indicates an ascending sort order.

      • A negative integer value indicates a descending sort order. (If set to a negative number, the absolute value of the number determines the column's position in the sort sequence.)

      • The default value of 0 indicates that the column is not sorted. Leave the value of 0 for output columns that do not participate in the sort.

      As an example of how to set the SortKeyPosition property, consider the following Transact-SQL statement that loads data in a source:

      SELECT * FROM MyTable ORDER BY ColumnA, ColumnB DESC, ColumnC

      For this statement, you would set the SortKeyPosition property for each column as follows:

      • Set the SortKeyPosition property of ColumnA to 1. This indicates that ColumnA is the first column to be sorted and is sorted in ascending order.

      • Set the SortKeyPosition property of ColumnB to -2. This indicates that ColumnB is the second column to be sorted and is sorted in descending order

      • Set the SortKeyPosition property of ColumnC to 3. This indicates that ColumnC is the third column to be sorted and is sorted in ascending order.

    9. Repeat step 8 for each sorted column.

    10. Click OK.

    11. To save the updated package, click Save Selected Items on the File menu.


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Tuesday, March 20, 2012 10:03 AM