More Merge Join question

All,

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?

Thanks

Here is more detail:

With join with input2, the records in input1 will have multiple records based on the months between the service date and system date.

The query for selecting input2 is a select distinct from a table

Thanks

|||

Here is more detail:

In input1, the records look like this:

EmpID, Dept_ID, service_date

12320060101

220 20060502

Input2 is a time dimension,

MonthKey, firstdayOf_month, lastdayOf_Month

2006012006010120060131

2006022006020120060228

*

*

*

2006052003050120030531

the data of input1 and input2 are from two different servers.

If use link server, the query looks like

Select empID, edpt_ID, service_date

From input1, linkserver.input2

Where input1.servicedate <= input2.MonthFirstDay

And input2.MonthFirstDay <= getdate()

The records returned from the query:

empID, dept_ID, service_date, monthkey

12320060101200601

12320060101200602

12320060101200603

12320060101200604

12320060101200605

22020060201200602

22020060201200603

22020060201200604

22020060201200605

In my SSIS package, I try not to use link server, so I use two OLE db source to query data and the data from input1 have some loop up process to go. At the end, input1 have to join with input2, which transformation should I use to join with those two inputs and what kind join should I use here?

Thanks a lot

|||

I can't think of any way to do this without writing script - in this case I actually think the linked server approach may be most efficient.

Donald Farmer