Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

SSIS Merge Join


I am working on an ssis package and i find an problem while using the merge join for merging 2 OLEDB Data sources .
data source 1 is : - The table formed my an sal server comand , that out put is given to a multicast since i want to sare that output amoung 2o other tables.

So the the left input for the merge join is OLEDB source , which contains direct data from source table
I am usong Inner join on one column
The problem is i am not getting the expected rows as out put of merge .
I tried to join the two tables in sqlserver query window and i am getting expected result
What could be the problem
The first table is
second table is  Out put of the following query

Select Distinct B.ReservationID as R

from Property.Main A ,Reservations.Reservations B ,Reservations.ReservationRooms C


A.propertyID = B.PropertyId And

C.ReservationID = B.ReservationID And

getdate() >=C.Until +A.ReservationOffLineDays
i am not getting the expected result here in SSIS package merge join
But if i try to execute the following in query editer in management studio i am getting the expected result !!

declare @temp as table

(ResID Varchar(50)


Insert into @temp


Select Distinct B.ReservationID as R

from Property.Main A ,Reservations.Reservations B ,Reservations.ReservationRooms C


A.propertyID = B.PropertyId And

C.ReservationID = B.ReservationID And

getdate() >=C.Until +A.ReservationOffLineDays

select * from Reservations.ReservationManual A , @temp b

Where A.reservationID = b.resID

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Complex SSIS Lookup/Merge Join Using NK And Dates
I had this (what seems to be a) simple question asked today and I'm afraid I didn't like my answer. Does anyone know the proper answer to this one:

Any ideas on how I can constrain a lookup or merge join based on the dimension row's effective and expired dates so three criteria are needed as follows:
1. DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND
2. DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND
3. DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey

 -- Brian

View Replies !   View Related
Problem Using Merge Join Transformation In Sql Server 2005(SSIS)
 I am pretty new to SSIS. I am transferring some rows from 2 source tables to 1 destination table.
 The 2 source tables have 1000 rows.They act as the 2 inputs to a merge join transformation where i perform the join between the 2 tables based on a couple of fields. But for some reason the output of  the merge join gives me about 1018 rows .Shouldnt the destination also have only 1000 rows?
 How do i solve tis problem?
Thanks in advance

View Replies !   View Related

I need to run an Insert query which pulls data from a table located on server A database AA Table AAA conditional on (or JOINED with) Table BBB in database BB sever B. In SQL 2000 it could be done as:

From Server A:
sp_addlinkedserver B
INSERT dbo.ResultsTable
SELECT SourceTable.* FROM  B.BB.dbo.BBB SourceTable
INNER JOIN A.AA.dbo.AAA ConditionTable  ON SourceTable.RecID = ConditionTable.RecID
sp_dropserver B

In SSIS one of the possible solutions is to use a package which does the following:

The problem with this approach is that it's extremely slow for large datafiles (50M records each)


1) In the procedure above could the SORT step be avoided?
2) Is there another approach to run cross-servers JOIN in SSIS?

Thank you

View Replies !   View Related
'Left Outer Merge Join' Failing To Join Valid Row

OLEDB source 1
      ,[MANUAL DCD ID]  <-- this column set to sort order = 1

OLEDB source 2
      ,[Bo Tkt Num] <-- this column set to sort order = 1
  FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC

These two tasks are followed immediately by a MERGE JOIN

All columns in source1 are ticked, all column in source2 are ticked, join key is shown above.
join type is left outer join (source 1 -> source 2)

result of source1 (..dcd column)
4-400-8000122 <--row not joining

result of source2 (..tkt num column)
4-400-1000122 <--row not joining

All other rows are joining as expected.
Why is it failing for this one row?

View Replies !   View Related
Merge Join (Full Outer Join) Never Finishes.
I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?

View Replies !   View Related
How Would You Convert A Hash Join Into A Merge Join?
I read that merge joins work a lot faster than hash joins.  How would you convert a hash join into a merge join?  (Referring to output on Execution Plan diagrams.)

View Replies !   View Related
Super Join - Is Merge Join The Answer?
Is there a way to do a super-table join ie two table join with no matching criteria? I am pulling in a sheet from XL and joining to a table in SQLServer. The join should read something like €œfor every row in the sheet I need that row and a code from a table. 100 rows in the sheet merged with 10 codes from the table = 1000 result rows.

This is the simple sql (no join on the tables):

select 1.code, 2.rowdetail
from tblcodes 1, tblelements 2

But how to do this in SSIS?

Thanks - Ken

View Replies !   View Related
Merge Join = 99%
I have got a query in which a merge join is 99% of the cost .... and I am confused ... is not merge join supposed to be the fastest ??? Anyone seen this before ???

Any ideas why this could be happening ... and sorry ... do not ask me to post the code coz I will not be able to ...

View Replies !   View Related
Merge Join
I need to use Merge Join transformation to join two sources. One is from a PIVOT transformation and one of the output columns is ISSORTED, the other is from an OLE DB Source using a query. The Merge Join transformation requires both input source have to be sorted. I cannot find the ISSORTED property on the OLE DB Source!!
I tried to use Derived/ copy transformations but cannot find the property also. How can set the OLE query sorted in order to use the MergeJoin?
Thanks a lot

View Replies !   View Related
Merge Join - HELP

I have a SQL Statatment:

      ON A.X= B.X
    AND A.Y= B.Y
When i execute this code in sql server returns 549 lines. I created a package with two oledb sources one for each table, sorted the tables with fields X and Y after placed a Merge Join with the fields:
A.Y join B.Y order 1
 A.X  join B.X  order 2
both fields with the Join Key checked
But my package return 411 lines.
What's happened?? :(
When a i have the code:
      ON A.X= B.X
When i did the join only one field SSIS worked fine, sql server returns 622 and SSIS returns 622 lines.
Please help-me...

View Replies !   View Related
Merge Join

I have a Merge Join transformation and when i sort values in OLEDB source the merge join fails, but if i use a sort transformation it works! Why??
Best regards,

View Replies !   View Related
When Should We Use Merge Join?

Hi, all experts here,
Any advices for when will be a better way of using Merge join instead of other options?
Thank you very much and I am looking forward to hearing from you shortly.
Best regards,

View Replies !   View Related
Merge Join Help
I am new to this SSIS.
I have a simple join query like this
select from tbl_a a, tbl_b b where = and I want insert the result to my temp table.
the query results is 1500 rows.
but when I use merge join in SSIS, it only inserts to my temp table 4 rows.
I use inner join and I already set the IsSorted to true and specify the sort position for the columns in both source tables
In tbl_a, there are one million rows, in tbl_b, there are 2000 rows.
I don't know why the merge join cannot work out my task.Is there other way that I can just run this simple join query in SSIS to copy the data?
Please help, thanks in advance.

View Replies !   View Related
Merge Join
Hello all,

I have a package where I use merge join for two sorted inputs and the output is stored in a raw file.

In another package, the raw file from above package is again merge joined with another sorted input. Now my question we need to sort again the raw file from first package? or is it OK to set the isSorted property to True and define the sort keys?


Thank you.

View Replies !   View Related
How To Join/merge Tables
Hi guys! I'm trying to figure out how to join 3 tables, but I can't seem to find a solution. What I want to do is to put table 1, table 2 and table 3 into table_merged.
table_merged = table 1 + table 2 + table 3
 Is it possible to merge tables even if they have different fields?
Please help.
Onegai shimasu... 
Thanks in advance!

View Replies !   View Related
About Merge Join Component
Hi, I'm using a Merge Join Component of Inner Join type to retrieve from the right pipeline some records to append to the ones coming from left pipeline according to the join citerias defined on the compnent.
Is there any way to know which are the records coming from the left pipeline that doesn't match the join criterias?
In the following I'll try to do an axample.
LF pipeline:
Column0           Column1           Column2
1                      aaaa                 aa11
2                      bbbb                 bb11
3                      cccc                 cc11
4                      dddd                 dd11
RT pipeline:
ColumnA          ColumnB         
1                      aa22
4                      dd22
On exiting from the MergeJoin, defining €œColumn0€? for LT  as join key and  €œColumnA€? for RT and as output data all the columns of the LT pipeline and the only ColumnB from the RT pipeline it should be obtained the following records:
Column0           Column1           Column2           ColumnB         
1                      aaaa                 aa11                 aa22
4                      dddd                 dd11                 dd22
and the records from the LT pipeline:
2                      bbbb                 bb11
3                      cccc                 cc11
shouldn't go in the output from the Merge Join Component.
What I need to know is which are these last lines because I need to manage them.

View Replies !   View Related
Merge Join Question
Hi, All,


  In the data flow of Intergration Service,  my understanding is that the "Merge Join" only does the "join", there is no way to input the "where" clause. If I need to input the the "Where" clause or selection criteria, how should I do it in Integration Service?




View Replies !   View Related
Merge Join Output Bug?
I've run into something that looks like a bug to me but I wanted to run it by the board:

Merge join 2 sorted tables.

Table1: ColumnA : Sort Order 1, ColumnB Sort Order 2

Table2 : ColumnA: Sort Order 1, ColumnB Sort Order 2, ColumnC not sorted

Merge Join the two tables on ColumnA and ColumnB...

Choose the following as output columns

A + B + C = works

C = works

A + C = works

B + C = NOT work.. error message: The column with the SortKeyPosition value of 0 is not valid. It should be 2. 

Basically if you choose one or more of the sorted columns in the output at least one of them has to be the column with Sort position 1 or you'll get that error. 

Is this a bug or intentional?  If you do not have sort column 1 in the output that output could no longer be considered sorted... so perhaps the error is related to that (instead of error I'd expect some warning about the sorting).  Interesting that it lets you choose C only becuase that also makes the output unsorted.


View Replies !   View Related
Merge Join More Than 2 Tables
I need to take certain items of data from four different tables and out them into one table.

Unfortunately my source data's version of SQL does not support the LEFT JOIN keyword which has left me with a bit of a problem.

I saw the merge join in SSIS and used it to get data from two of the tables and stick them in the destination and it all worked fine.

That got me thinking, is it possible to create a second merge join transformation within the same data flow task for the remaining two tables and then join the output of both the merge joins to give me the data I need from all four tables in one output?  




View Replies !   View Related
Merge Join Or Union All

I am trying to normalize data using the unpivot transform. I have to unpivot using more than one key so I have a multicast feeding into two unpivot transforms then into a sort transform. This is where my problem starts - I have tried using a Merge Join (inner Join) transform but dont get the expected result.

My original data looks like this:











After the mulitcast - unpivot - Merge Join, the expected result is: (pk_newID is an identity)













However with a Merge-Join (inner join on pk_ID) I get





















Is the Merge Join transform not the right choice?


View Replies !   View Related
Merge Join Problem
I don't know if anyone is having this issue with merge join. I built a custom SCD to handle insert, update and delete records. So far I am having success with the delete but after I do a union all combining the updated and new rows, I merge them with the source. My proble is that is I have 10 records coming from the flow and I have say 30 records from the source, it will only load about half of these records. IBy the way, I am doing inner join. When I copy the two set of data and I manually join them, I get the result I expect. Is there something I am missing in the merge join configuration?

View Replies !   View Related
More Merge Join Question
I want to use Merge Join to join two sources, 
but the join condition should like this
and input1.servicedate <= input2.MonthFirstDay
and input2.MonthFirstDay <= getdate()
Does Merge Join support this or What€™s the best way to do it?

View Replies !   View Related
Possibly Merge Join Bug?
i'm merge joining 2 data sources, one is oracle and the other is excel...the problem is in the oracle source, it's a sql statement like:

select hdr.div_ord_no, hdr.mtr_no, hdr.prod_cd
from     qctrl_div_ord_header hdr,
 (select max(sub.eff_dt_from) min_eff_dt_from,    div_ord_no
 from qctrl_div_ord_header sub
  group by div_ord_no
 ) tmp
where    hdr.eff_dt_from = tmp.min_eff_dt_from
and  hdr.div_ord_no = tmp.div_ord_no

having that sql statement, merging will come out with 0 rows 

however, having a simple query like:

select hdr.div_ord_no, hdr.mtr_no, hdr.prod_cd
from     qctrl_div_ord_header hdr

merging will come out with 2 rows

you may think that the data in the first sql statement is not there for the merge, which causing the 0 rows, however, the data is there, i'm only joining by one column and definitely the data is there, the merge result should be 2 rows for both query statements

i believe this is a problem with SSIS, anyway around this?

View Replies !   View Related
Merge Join Problem
I've got a problem with the merge join operator.

I'm trying to Join two tables using a left outer join.

Here are my tables :


600 records in this table.

AA; Maradona;Diego;
BB; Maradona;Diego;
DD; Zidane;Zinedine;

145 records in this table.

This is what i expect in my left outer join:

600 records in the destination table, with only 145 Not null first_name and last_name

issorted property has been set to true in the 2 sources tables, the
numkeyscolumns property is set to 1, and the join keys are both checked.
When i execute the sql query using the editor in sql server, the result is good, i've got what i've expected.
when i run the dataflow, the destination has got 600 records, but the
columns FIRST_NAME and LAST_NAME are not null only 8 times, the 592
other lines are NULL.
Could someone explain to me why i lost 145-8=137 FIRST_NAME and 145-8=137 LAST NAME ?
Thank you in advance.

View Replies !   View Related
Merge Join Transformation
 i have 2 input files,those are sorted on account no field , i use to merge these 2 files data into 1 file.i took merge join trans.... with inner join but i didn't get the output fields ,plz help me regarding this issue.......

View Replies !   View Related
Help Needed With Merge Join
I am trying to use the merge join example in the following link. To import new records only.
The problem is that for some unkown reason the join is not woeking correctly. One of the records is incorrectly showing a NULL on the output. This would indicate that it would be a new record, but it is not it already exists in the new table.
I created a dummy table in SQL and executed the same join and I always get the right answer. What the heck could be wrong?
For example. Table A has 20 records  Table B has 3 records. Table B has the new records I want to import into Table A. The package runs corectly the first time, only importing the 3 new records. Then the next time the package runs it shows 1 of the 3 records as being new still, and tries to import the record causing a PK error. Adding a watch to the MERGE output shows that the one record has a NULL on the join.
Please help this is driving me nuts.

View Replies !   View Related
Merge Join Comparison
I'm trying to compare two fields between two tables using a Merge Join that runs into a conditional. This conditional sorts mismatched rows from validated ones but its returning incorrect mismatches (which means the mismatch is actually a match).
TABLE1 has two DT_STR fields with length 16 and TABLE2 had two DT_STR fields with length 32.
I've used a data conversion component to lengthen the first table's fields to a length of 32 but there still seems to be incorrect mismatches. For example TABLE1 had "AAA" and "BBB" and this appeared as a mismatch. If I used Query Analyzer to check TABLE2 for this criteria it would exist (which means it IS a match).
Is there any way to view hidden characters (i.e. carriage returns, tabs) in those fields? I've tried using RTRIM in my SQL Query for both my data sources and they still don't match up.

View Replies !   View Related
0xC0204006 In Merge Join Component
I have data coming from 2 sources
Source 1 (EmpId, EmpName, Hiredate, Salary)
Source 2 (MgrId, MgrName, IsActive, Date_Started, Wages)

I am only concerned about 3 common fields (Name, Date and Wages)

I have joined EmpName with MgrName, Hiredate with Date_Started, but when I select Wages in correspondence to Salary, it shows me the following error


Error at Manager_Detail [Merge data with adjustments (Salary) [11668]]: The metadata for "input column "Wages" (13852)" does not match the metadata for the associated output column.

Error at Manager_Detail [Merge data with adjustments (Salary) [11668]]: Failed to set property "OutputColumnLineageID" on "input column "Wages" (13852)".

 (Microsoft Visual Studio)

All the datatypes are matching, but I dont know why this error occurs

Any help in this will sincerely be appreciated.

View Replies !   View Related
Merge Join With Empty Inputs
Hi all,

Does anyone have suggestions for ways to deal with the chance that a merge join might receive empty inputs?

I've noticed that when this happens the transformation seems to hang.  I changed the MaxBuffersPerInput to zero and this seems to cure the problem but I'm not sure it's the best way to deal with it.

Would it be a good idea to test the row counts with a conditional split before such a join?



View Replies !   View Related
Merge Join Transformation Hangs
I have serached through this forum, and I could not find the solution.

My SSIS data flow is a simple one.  Extracting two tables from Oracle using  OLE DB Source , join them together using merge join and loading into a table in SQL server by SQL Server Destination.

I haven't gone through this simple procedure because Merge join always hangs there. Down to further investigation, I found one input (randomly one of two inputs) is always stuck. Sometimes the input is empty, sometimes is about half way.

Is there workround to see what is happening there and to fix this problem?



View Replies !   View Related
Merge Join's Poor Performance
Hello All,

I'm experiencing performance problems with the merge join task.
Every time I'm building a nice package using this task, I'm ending up deleting it and using SQL statement in the OLE DB source to accomplish the join since it takes forever to run and crushing my computer at the process.
It makes me feel I don't use the abilities SSIS has to offer compared to DTS.
Of course for the use of several thousands of records it works fine, but in a production surrounding with hundred of thousands of rows, it seems to be futile.

Maybe someone had a little more luck with it?


View Replies !   View Related
Performance Problem With Merge Join
We are using SSIS to transfer data from OLTP database to DataWarehouse database on a daily basis. Our solution is modelled on / a copy of the (excellent) Project Real solution.
first stage of process, we extract the daily "records" into 4 stage files (raw file source)
Then we process these 4 files to populate our Fact and Dimension tables.
We are having a problem with the population of a couple of our dimension tables.
e.g, Comment dimension table - DimComment
Our aim is to only add records to the table that do not already exist
on "left side" of dataflow

1) read contents of stage file - just get commenttext column
2) use derived column component to add three columns (updatedby, lastupdated, ETLLoadID) and to Trim(commenttext)
3) use sort component to sort output - sort on commenttext and remove duplicates
on "right side" of dataflow

1) read the contents of the DimComments table

select ltrim(rtrim(CommentText)) as CommentText,
from DimComment
order by ltrim(rtrim(CommentText))
- remembering to set both the IsSorted and SortOrder properties for the component.

then we use a Merge Join component to merge the two dataflows. Within the Merge Join, we use a Left Outer Join so that we get all of the commenttext records from the daily stage file - which will have a CommentKey from the dimcomment table if there is a match (matching on comment text)
then we use a Conditional Split component to remove records from the data flow where the CommentKey is not null - i.e. we only want records that dont already exist onthe comment table.
finally, update DimComment table
Database table not being updated correctly - has duplicates
Problem appears to be with the Merge Join component.
From the existing records on the DimComment table, we get 1,943,309 records
From the daily stage file, we get 2,578 records - that after the sort (and duplicate removal) is reduced to 776 records
After the Merge Join, this is reduced to 771 records - only the first 5 records are matched - but it should be reduced by more....
if i amend the reading of the DimComment table to have the following clause

where (commenttext like '%automation comm%')
then it returns 8,347 rows - and 20 rows are matched - correct
if i amend the reading of the DimComment table to have the following clause

where (commenttext like '%automation com%')
then it returns 603,286 rows - and 358 rows are matched - correct
if i amend the reading of the DimComment table to have the following clause

where (commenttext like '%automation%')
then it returns 899,462 rows - and 0 rows are matched - incorrect
if i amend the reading of the DimComment table to have the following clause

where (commenttext like 'a%' or commenttext like 'b%'

or commenttext like 'c%' or commenttext like 'd%' or commenttext like 'e%')
then it returns 899,462 rows - and 29 rows are matched - incorrect - did most of them - but not all ???
in theory - if i run the process twice, i should get NO updates second time through - this is NOT the case !!
it would appear that in some cases, the Merge Join component is doing its merge join before it has got all of its records from the DimComment dimension table - matching appear to work on diffferent sets of records - as long as there are not too many records to process
Question 1
is it possible to correct this ?
Question 2
is there a limitation on the processing of the Merge Join component in number of records it can handle ? - if so, what is it ? - we have two other SSIS packages doing similar processing to this (but not as many records - YET)
(tablerow structure on dimensions tables is short - only 3-4 columns per row)
Question 3
might it be better to process the daily comments by after doing the sort, doing a lookup on the DimComment table - and then inserting into the DimComment table if there is no match found
above details got from running app on my PC on local database (SQL2005) - database is a copy of production (couple of weeks old)


View Replies !   View Related
Merge Join Vs Insert Then Update: What Is The Best ?

I have to  load 2 flat file of 2 Gb each in a table. I found 2 ways to do that:
- Either I read my 2 file in parallels and I use a merge join transformation to merge my data. Then I load theses datas in my destination table
- Or I insert all my first file in the destination table then I read the second file and update my table with the new information.

Based on your experience, what is the best ? I'm running the first way for more than 30 minutes and SSIS is still sorting my datas.... (sort before the merge join)

Thanks in advance,

Knid regards

View Replies !   View Related
Merge Join - No Output Rows
I have a problem with a Merge Join providing no output (when it should have 1890 rows).  My Data Flow Task has 4 OLE Data Sources, 3 Multicasts, and 1 OLE Data Destination.  I am experiencing the problem near the end of my data flow where two Multicasts create two parallel flows of data (see Level 1 below).  I have two Merge Joins which join one leg from each multicast with a leg from the other multicast (see Level 2 below).  Then the two remaining legs use a Merge to get my destination output (see Level 3 below).
I am experiencing my problem with the Merge Join (input A2, B2) --> (output C2) transformation.  The Merge Join providing output C1 appropriately outputs 1890 rows, but C2 outputs 0 rows.  Both Merge Joins are identical.  The data is identically sorted prior to entering the problematic Merge Join and a DataViewer (Grid) verified that the data is appropriately entering in.  Merge Join (input A2, B2) --> (output C2) has 667 rows as input A2 and 1890 rows as input B2 (using an inner join, just like the other merge join),  but C2 baffles me with 0 rows of output (when it too should have 1890).  I receive no Ouput errors and the execution completes showing all green.
Level 1
Multicast (output A1, A2) [667 rows]
Multicast (ouput B1, B2) [1890 rows]

Level 2
Merge Join (input A1, B1) --> (output C1) [1890 rows]
Merge Join (input A2, B2) --> (output C2) [0 rows]
Level 3
Merge (input C1, C2) --> (output D1) [1890 rows]*

I read about mysterious behavior with Merge Joins and have attempted modifying my EngineThreads property to values between 2 and 10, with no luck.  Any help/ideas would be appreciated.
* Should be 3780 rows

View Replies !   View Related
Merge Join - Output 0 Records
I have a sql statement that joins two tables and I get back a few thousand records when I run it in query tool in management studio.
But when I use SSIS merge join to join the two tables my output is 0 records.
I did sort the key column in both tables  by setting 'sortkeyposition' property to 1 in advanced editor for output of both tables.
however the merge join returns nothing to my destination tables. Also I am doing a inner join. The task runs without error but returns nothing as well.. any ideas?

View Replies !   View Related
Multiple Inputs In Merge Join?
hi guys,
just wondering if there's a SSIS component out there some what similar to Merge join but can take up more than two inputs to join.

basically i have a big package with data sources coming from everywhere, they all have a unique column i can join on, so right now, i use merge join for every two sources, then join the output of that to another source so on and so forth. it would be easier if i can just join all of the sources in one component rather than putting a merge join for every single join. is there such a component out there, custom built maybe?


View Replies !   View Related
Determine 'having Rowcount(ID) &&>1' After Merge Join?
I wish to perform different processing for data that has multiple rows after a merge join.


table 1               table 2
ID Value            ID Value
1   AAA             1   ZZZ
2   DDD             1   QQQ
3   GGG            2   MMM

so this gives (doing a left outer merge join on 'ID')

ID table1value table2value

but what can I do to now strip out ID 1



View Replies !   View Related
Example Of Merge ,hash And Nested Join
Could Any body Please give me one example of each of three types of joins that is
Merge  Join
Hash   Join
Nested join

View Replies !   View Related
Issue With Join Filters In Merge Replication
hi guys ..hope you all doing well... I am new to Merge Replication and I am having an issue using Merge Replication filters... I am sure somebody here must have come across the issue... I have 3 tables in my Publication Users, Orders, OrderItems... Users have 1-* relationship with Orders and Orders have 1-* relation ship with OrderItems.

In my application, Users create Orders and Order Items. While creating Order Items, an item can be assigned to a different user to review it. So OrderItems table also have AssignedUserID (foreign key with Users table) in it.

Now when the user syncs, I want to filter out all the Orders and OrderItems that belongs to that User.

To do this, In my Publication i have got a root parameterisze filter on Users table based on UserID. The Users table have a join filter with Orders tables which in turns joins with OrderItems. This is fairly simple and works smoothly. The join filters looks like

Users filter: Users.UserID = HostName()
---Orders filter: Users.UserID = Orders.UserID
---OrderItems filter: Orders.ID = OrderItems.OrderID

Now in addition to this, what i also want is to filter out all the OrderItems that have been assigned to the user. For this i put an OR condition in OrderItems to check on AssignedUserID. The filters look like this

Users filter: Users.UserID = HostName()
---Orders filter: Users.UserID = Orders.UserID
---OrderItems filter: Orders.ID = OrderItems.OrderID OR OrderItems.AssignedUserID = HostName()

...but when i sync, the OrderItems with AssignedUserID doesnot get downloaded. I am not sure how to achieve this? Deos anyone have any idea how can i do this?

I have treid different combinations of Joins but no joy. somestimes it gives me foriegn key violation error and sometimes it downloads all the Users, Orders and OrderItems without performing any filtering.

View Replies !   View Related
Merge Join Conditional Split - HeadBanger

I have a merge join that does a full outer join. I then have a conditional split that will breakout  by unchanged, insert and update. The update is what I am having a problem with. The conditional split for the update  is a follows:


Now I believe the problem is related to spaces in the key field let me explain.

The join field is defined as [char](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

There was an original mismatch between the two tables of VARCHAR(14) & Char(14) but now there both Char(14).

What's interesting is that the few records with Alpha characters correctly does the update but the records with starting numeric data all go down the insert path.

The data of the join columns look like

'308811151     '    - 5 Spaces           This Data incorrectly goes to insert path

'TSTRWR02      '    6 Space             This Data correctly goes to update

The data is grouped and sorted by the Key field and the historical and transform column contain a 1 for all records.
while the Doc_nbr which is the key

I tested the join in SQL server and it works with and without the spaces.

From SSIS I also tried RTRIM in the SQL command of the source in addition to substring the first 9 with the right trim and I always received the same results.

I am guessing there is something going on with the not isnull in the conditional split but I can't figure it out if I am missing something or if this is a BUG.

Three paths of data after the merge join and the conditional split

206352185     ,,1,
209900165     ,,1,

NEE           ,1,1,NEE          
New           ,1,1,New 


Any suggestions, am I missing the obvious?







View Replies !   View Related
Merge Join: The Clolumn With The SortKeyPosition Value Of 0 Is Not Valid. It Should Be 4.
I have a Data flow task with two OLE DB sources, which are both sorted by same columns (order by) and IsSorted, sortKeyPosition are both set.
Then I have a Merge join (left join) where I get validation error: The clolumn with the SortKeyPosition value of 0 is not valid. It should be 4.
However this only happens if I include a join key column also as output.
Can columns be both join keys and output?
Thanks, Janez

View Replies !   View Related
Removing Path To Merge Join Causes DTS_E_OBJECTIDNOTFOUND
When I use the object model to try to remove a path leading to a Merge Join component, I get an error. Anybody have any workarounds? (I'm sure there is a workaround because I'm able to delete that same path via BIDS without errors.)
Though I'm trying to remove a path from a package created with BIDS, the following is an easy way to reproduce the error. The last line in the following code is the one that generates the error.

Code Snippet

Application a = new Application();

Package p = new Package();

TaskHost th = p.Executables.Add("DTS.Pipeline") as TaskHost;

th.Name = "DataFlow";

MainPipe pipe = th.InnerObject as MainPipe;

//add a connection mgr

ConnectionManager adventureWorks = p.Connections.Add("OLEDB");

adventureWorks.Name = "OLEDBConnection";

adventureWorks.ConnectionString = "Provider=SQLNCLI;Integrated Security=SSPI;Initial Catalog=AdventureWorks;Data Source=(local)";

//add the OLEDB source component
IDTSComponentMetaData90 oledbSource = pipe.ComponentMetaDataCollection.New();

oledbSource.ComponentClassID = "DTSAdapter.OLEDBSource";

CManagedComponentWrapper instance = oledbSource.Instantiate();


oledbSource.Name = "OLEDBSource";


= p.Connections["OLEDBConnection"].ID;


= DtsConvert.ToConnectionManager90(p.Connections["OLEDBConnection"]);

instance.SetComponentProperty("OpenRowset", "[Production].[Product]");

instance.SetComponentProperty("AccessMode", 0);




//add the merge join component

IDTSComponentMetaData90 mergeJoin = pipe.ComponentMetaDataCollection.New();

mergeJoin.ComponentClassID = "DTSTransform.MergeJoin";

mergeJoin.Name = "merge";

instance = mergeJoin.Instantiate();


oledbSource.OutputCollection[0].IsSorted = true;

oledbSource.OutputCollection[0].OutputColumnCollection[0].SortKeyPosition = 1;

pipe.PathCollection.New().AttachPathAndPropagateNotifications(oledbSource.OutputCollection[0], mergeJoin.InputCollection[0]);


a.SaveToXml(@"c:projects est ssisSimplePipeline.dtsx", p, null);

//if I skipped the next 3 lines, I wouldn't get the error

p = a.LoadPackage(@"c:projects est ssisSimplePipeline.dtsx", null);

th = p.Executables[0] as TaskHost;

pipe = th.InnerObject as MainPipe;


int iPathToRemoveID = pipe.PathCollection[0].ID;

pipe.PathCollection.RemoveObjectByID(iPathToRemoveID); //Exception from HRESULT: 0xC0047072 (which is ErrorCode -1073450894 which is DTS_E_OBJECTIDNOTFOUND)

Some things I noticed:
1. Note the comment that says "if I skipped the next 3 lines, I wouldn't get the error". Apparently the error is somehow related to saving the package to disk then reloading it from disk. (By the way, I have to load it from disk because the package has been previously generated via BIDS, not by code. The above sample is just a simple way to repro the problem.)
2. I don't get the error with some other components (like union all). I haven't tried all the different stock components yet, but it appears to be something specific to merge join (or at least components like merge join).
I'm on SP2 proper... not any cumulative updates. (And I would like a workaround for SP2 proper.)
Thanks for any suggestions.

View Replies !   View Related
Dynamic Column Sorting After Merge Join

I have data coming in from two sources, one being SQL and the other being Oracle. The end result needs to be a CSV file with the columns in a specific order. I have a Data Flow task setup that takes both sources and does a Merge Join on them. I can add a Sort Transformation and manually set the sorting of all 156 columns that end up going to a CSV file destination. However, I have a table setup that holds the names of the 156 columns and the order that the CSV file expects them to be in. I would much rather do this step dynamically as the column names and order may change in the future. Anyone who has used the Sort Transformation for a large number of columns knows how tedious it can be and how adding a column in the middle will cause you to change the sort # for each of the columns that come after it.
So I added a Script Component between the Merge Join and the Flat File Destination hoping that I could alter the order of the columns there. However I added the following code and found that the SortKeyPosition is ReadOnly.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim column As IDTSInputColumn90

For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection

column.SortKeyPosition = 1


End Sub
I was hoping to add some code to get the sort index from my table for each of the columns and set it to the SortKeyPosition. Has anyone out there done this before or seen an example that might point me in the right direction? I've searched for 2 days without coming up with much.


View Replies !   View Related
Strange Behaviour Sort And Merge Join
I have encountered an annoying problem which causes the Merge Joins to lose records in the dataflow. The problem is caused by 2 unusual behavoirs.

1/ Sort of SSIS is not sorting the same as ORDER BY in SQL

Code Snippet

CREATE TABLE [dbo].[table_2](
[test] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

With data as following:

Code Snippet1000

When select this data with an order by like: select test from table_2 order by test
The result will be:

Code Snippet

If you sort the data by the SORT block of the SSIS the result will be:

Code Snippet

2000This is annoying and dangerous, because it causes the next bug.

2/ Two datasources sorted by ORDER BY clause can give problems in a Merge Join.

If you have 2 data sources both correctly sorted by an order by in the query. When you join these 2 datasources with a Merge Join, you can lose some records in the dataflow.
This happens with larger datasets than examples above.

When I join the datasources (see image ) inside SQL I will get a correct result of 15271 records.

Is this a bug which I should report? or is there a flaw in my logic?

View Replies !   View Related
Merge: Records Associated By A JOIN Filter Not Being Sent To A Subscriber

I have a merge (SQL 2005 Standard -> Express) topolgoy which is having problems 

The main problem is that the join filters don't seem to work for one area and I am hoping someone can help me with some troubleshooting advice

There are 140+ tables in the topology but the ones causing  particular  pain are a parent child relationship where the child is actually a bridge/linking table to another table.

Therefore although it is a parent child in the replication filters it is the reverse. i.e. the child has the paramterised filter on it and the parent is one level down joined by it's id.  There are other tables joined to this parent table but it stays at three levels deep.  The @join_unique_key therefore is set to 0 as is the partition options for the parent /child relationship.

However, when we synchronise we have a problem. The rows get inserted in to the database in RI order but only the child records are replicated down to the subscriber.

The child table with the parameterised filter has 13 articles joined to it in total and one of the other branches of join filters go down as deep as four levels.  Most though do not.

Does anyone have any suggestions as to why this might be happening?  Any help would be greatly appreciated.

Cheers, James

P.S. I should add this problem only occurs when the edits are made at the publisher.  If new records are added at the subscriber everything is fine.

View Replies !   View Related
Only 9999 Rows After MERGE JOIN In SQL Server BIDS
I've gote 2 Tables with about 50.000 rows and I left outer join them with MERGE JOIN.

The result are 9999 rows. Has anybody got the same problem. Maybe it's a bug!?

View Replies !   View Related
Merge Join - Output Of Lookup As Sorted Field?

I'm doing a data conversion with one of my fields (SUMDWK) from one of the tables that will be used in a merge join.  With the new, converted field, I do a look up.  From this look up, I want to take a new field FiscalWeekOfYear, and replace the original field, SUMDWK.  This is necessary because SUMDWK is one of the sorted fields.  In the look up, it is not possible to change the Output Alias.  Does anybody know a way around this?  Thanks.

View Replies !   View Related
Merge Join Vs. Lookup Vs. Custom Script - Which Is Fastest?
Very often we have 50'000+ rows which you need to pull values from different source (e.g. CityNames from citycode in Excel file). Currently we are using Lookup - but the questions is which of those 3 options is best in performance wise?

View Replies !   View Related
Merge Join: Nr Of Output Rows Unchanged When Amount Of Input Changes
Dear all,


I created a package that seems to work fine with a small amount of data. When I run the package however with more data (as in production) the merge join output is limites to 9963 rows, no matter if I change the number of input rows.


Situation as follows.


The package has 2 OLE DB Sources, in which SQL-statements have been defined in order to retrieve the data.

The flow of source 1 is: retrieving source data -> trimming (non-key) columns -> sorting on the key-columns.

The flow of source 2 is: retrieving source data -> deriving 2 new columns -> aggregating the data to the level of source 1 -> sorting on the key columns.

Then both flows are merged and other steps are performed.


If I test with just a couple of rows it works fine. But when I change the where-clause in the data source retrieval, so that the number of rows is for instance 15000 or 150000 the number of rows after the merge join is 9963.


When I run the package in debug-mode the step is colored green, nevertheless an error is displayed:

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Merge Join" (4703) failed with error code 0xC0047020. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.


To be honest, a few more errormessages appear, but they don't seem related to this issue. The package stops running after some 6000 rows have been written to the destination.


Any help will be greatly appreciated.


Kind regards,



View Replies !   View Related

Copyright © 2005-08, All rights reserved