Sorting Merge Join output

Question

I have two merge joins in data flow task. I want to set the IsSorted property for inputs of second merge join. But it is giving error as "The IsSorted Property must be set to True on both sources of this transformation." following is the image of this:

UPDATE 1

From the answer and the comments below, the IsSorted property can be found in the Data sources (Excel + OLEDB) advanced editor. But the Merge Join Transformation doesn't have this property. And i need to Merge the first Merge Join output with the Excel Source without using a Sort component.

I think you have to use "Sort" data flow task before adding input to "Merge" task. — DatabaseCoder, Feb 26 '18 at 10:30
I tried to reproduce your problem, but couldn't. I have input of merge join as another merge join. Make sure your `practice locations` is sorted via advance properties. — Prabhat G, Feb 26 '18 at 11:22
But I think Union All works as union operation in sql server. I don't want to do that. I just want to do left join operation. — Amol R, Feb 26 '18 at 11:27
Thanks Prabhat for reply. The practice location is already sorted. The main issue is we cannot set the IsSorted property for first merge join. The property is available for data sources only and not for intermediate transformations like merge join. — Amol R, Feb 26 '18 at 11:29
Right click on your data source and select `Show Advanced Editor...`. The `IsSorted` property is in the `Input and Output Properties` Tab — iamdave, Feb 26 '18 at 11:32
@AmolR : I tried to replicate your issue. Kindly double check. Merge-join when taking input as another merge-join and source, it only needs `is sorted` for source. In my case, it perfectly worked. Just make sure your second source has 'is sorted' correctly put. ssis won't understand even if your source is already sorted. you'll have to set it manually. — Prabhat G, Feb 26 '18 at 11:38
@Prabhat: I tried adding sort transformation for second source, but still it is throwing that error. — Amol R, Feb 26 '18 at 11:54
I suggest you import the excel file into a staging table and do the join in the database. Doing it this way is kinda complicated — Nick.Mc, Feb 26 '18 at 12:49
Possible duplicate of [SQL Server Integration Service Left Join](https://stackoverflow.com/questions/27656932/sql-server-integration-service-left-join) — Tab Alleman, Feb 26 '18 at 14:32
@TabAlleman i don't think this is a duplicate, he is asking to sort the merge join output. — Hadi, Feb 26 '18 at 15:14

Hadi · Answer 1 · 2018-02-26T15:11:53.973

2

Update 2 (Workaround)

After the merge join add a script component in which you will add one output column (will be used as second join key). In the script just assign the original key value to this column.

Then in the script input and output properties, set the Output IsSorted property to True

Example:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Output0Buffer.AddRow()
    Output0Buffer.outEmployeeName = Row.EmployeeName
    Output0Buffer.outEmployeeNumber = Row.EmployeeNumber
    Output0Buffer.outLoginName = Row.LogineName


End Sub

Update 1

If your are looking to generate Sorted output from the Merge Join transformation, then i recommend you to follow this link:

Merge Join component sorted outputs [SSIS]

Initial Answer

The IsSorted property can be edited from the Advanced Editor,

Just right-Click on the OLEDB Source (or Excel Source if needed) , go to the Input and Output Properties, click on the Output and you will find the IsSorted property in the properties Tab

Then you souhld set the SortKeyPosition for the columns

edited Feb 26 '18 at 15:11

answered Feb 26 '18 at 11:41

Hadi

36,233
13
65
124

I did that but it only works for first merge join. It doesn't work for second one. – Amol R Feb 26 '18 at 11:48
If the next merge join is with the excel source then you must set the `IsSorted` property on the `Excel Source` – Hadi Feb 26 '18 at 11:52
@AmolR what are the inputs of the second merge join? – Hadi Feb 26 '18 at 11:57
First Merge Join output and Practice Locations output. – Amol R Feb 26 '18 at 11:59
@Did you set the `IsSorted` property for all inputs? – Hadi Feb 26 '18 at 11:59
That is the issue. The IsSorted property is only available for Excel source and not first Merge Join. So I cannot do it. – Amol R Feb 26 '18 at 12:02
@AmolR after searching for this issue, i think the only way is to add a sort component after the first merge join. – Hadi Feb 26 '18 at 12:15
@AmolR https://krazycoder.wordpress.com/2010/06/24/merging-two-or-more-tables-in-ssis/ – Hadi Feb 26 '18 at 12:25
1

Thanks for the link. But in that they used Sort transformation for sorting. I don't want to use that because of performance issues. I simply want to set IsSorted property and do the work. – Amol R Feb 26 '18 at 12:35
@AmolR i provided a new helpful link in the answer (UPDATE 1), check it out i think this is what you are looking for – Hadi Feb 26 '18 at 13:18
1

Be careful with IsSorted = true on an excel workbook. If data starts coming unsorted you will get duplicates in your load. I learned this lesson tha hard way. – KeithL Feb 26 '18 at 14:04
@KeithL i agree with that – Hadi Feb 26 '18 at 14:06
Thanks Hadi, Will try Update2 and let you know if it works. – Amol R Feb 26 '18 at 14:15
Hadi, I did not get where to set IsSorted property in Script Componenet. Can you elaborate more on this. – Amol R Feb 26 '18 at 14:31
@AmolR click on the Output and you have to set SynchronousInput property to none, then the `isSorted` property will appear. but then you should write the whole script (if you need more help writing the script then give me a feedback) – Hadi Feb 26 '18 at 14:56
@AmolR i added an example, take a look – Hadi Feb 26 '18 at 15:12

Sorting Merge Join output

1 Answers1

Update 2 (Workaround)

Update 1

Initial Answer