We are creating a framework for automating the creation of import from data sources. As a part of this we have created a function to create a merge join between two ole_db sources. Both sources are sorted, they have the IsSorted set to true, and they have sortkeys on the outputcolumns. But the merge only joins on the 1st sortkey If there are two or more sortkeys, the join is wrong. If I open the created package, and remove the links from the sources to the merge join, and add them again, the same thing happens., However if I delete the merge join and add it again, then it joins on all sorted columns. I have made a copy of a generated packege, using order details from Northwind. It can be found here: http://ge.tt/9rc3v6J?c The code for creating the merge join is here: public static IDTSComponentMetaData100 AddMergeJoin(MainPipe dft, string name, IDTSComponentMetaData100 left, IDTSComponentMetaData100 right) { IDTSComponentMetaData100 merge = dft.ComponentMetaDataCollection.New(); merge.ComponentClassID = "DTSTransform.MergeJoin.2"; CManagedComponentWrapper mergeDesigntime = merge.Instantiate(); mergeDesigntime.ProvideComponentProperties(); merge.Name = "MRG "+name; Console.WriteLine("merge created "); merge.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false; merge.InputCollection[0].HasSideEffects = false; merge.InputCollection[1].ExternalMetadataColumnCollection.IsUsed = false; merge.InputCollection[1].HasSideEffects = false; //create path from source1 to merge IDTSPath100 pathSRC1merge = dft.PathCollection.New(); pathSRC1merge.AttachPathAndPropagateNotifications(left.OutputCollection[0], merge.InputCollection[0]); //create path from source2 to merge IDTSPath100 pathSrc2merge = dft.PathCollection.New(); pathSrc2merge.AttachPathAndPropagateNotifications(right.OutputCollection[0], merge.InputCollection[1]); IDTSInput100 mergeInput1 = merge.InputCollection[0]; IDTSVirtualInput100 vMergeInput1 = mergeInput1.GetVirtualInput(); foreach (IDTSVirtualInputColumn100 vColumn in vMergeInput1.VirtualInputColumnCollection) { mergeDesigntime.SetUsageType(mergeInput1.ID, vMergeInput1, vColumn.LineageID, DTSUsageType.UT_READONLY); } IDTSInput100 mergeInput2 = merge.InputCollection[1]; IDTSVirtualInput100 vMergeInput2 = mergeInput2.GetVirtualInput(); foreach (IDTSVirtualInputColumn100 vColumn in vMergeInput2.VirtualInputColumnCollection) { mergeDesigntime.SetUsageType(mergeInput2.ID, vMergeInput2, vColumn.LineageID, DTSUsageType.UT_READONLY); } IDTSCustomProperty100 property1 = merge.CustomPropertyCollection[0]; property1.Value = 2; IDTSCustomProperty100 property2 = merge.CustomPropertyCollection[1]; property2.Value = 1; IDTSOutput100 outt = merge.OutputCollection[0]; for (int i = (outt.OutputColumnCollection.Count/2); i < outt.OutputColumnCollection.Count; i++) { Console.WriteLine("out column: {0}", outt.OutputColumnCollection[i].Name); outt.OutputColumnCollection[i].Name = outt.OutputColumnCollection[i].Name + "_rigth"; } mergeDesigntime.SetComponentProperty("JoinType", 1); //left outer mergeDesigntime.AcquireConnections(null); mergeDesigntime.ReinitializeMetaData(); mergeDesigntime.ReleaseConnections(); return merge; } } }

And I found a solution. If I set the Property NumKeyColumns of the Merge Join component to the number of sortkeys, then the join works as expected. I added this code to the function to find the number of sortkeys: int NumKeyColumns = 0; foreach (IDTSVirtualInputColumn100 vColumn in vMergeInput1.VirtualInputColumnCollection) { mergeDesigntime.SetUsageType(mergeInput1.ID, vMergeInput1, vColumn.LineageID, DTSUsageType.UT_READONLY); if (vColumn.SortKeyPosition > NumKeyColumns) NumKeyColumns = vColumn.SortKeyPosition; }