Advice on DTS Package in Replication

Is it advicable to use DTS package as a tranformation media in transactional
replication or should transformation be done at Stored Procedure level on
Subscriber?
Under what circumstances it will be a good idea to use DTS package.
Thank you very much.
only use transformable subscription with hetergeneous subscribers. For SQL
Server subscribers there are other ways (stored procedures being one of
them) of transforming the data in route which offer far greater performance.
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:C9B7E873-C6A2-40CC-8EE5-0DAC1D1A7508@.microsoft.com...
> Is it advicable to use DTS package as a tranformation media in
> transactional
> replication or should transformation be done at Stored Procedure level on
> Subscriber?
> Under what circumstances it will be a good idea to use DTS package.
> Thank you very much.

Advanced lookup transformation

Hi,

Here is my problem for a lookup transformation:

I have an input flow with dates and fields like this :

ID BEGINNING_DATE ENDING_DATE

1 12/01/2006 12/16/2006

and a reference table like this:

ID PRICE BEGINNING_PRICE_DATE ENDING_PRICE_DATE

1 400 11/28/2006 12/03/2006

1 500 12/03/2006 12/06/2006

1 600 12/06/2006 12/09/2006

I have to get the intersection periods between the two tables joining on ID. I would like to have this result flow :

ID BEGINNING_DATE ENDING_DATE PRICE

1 12/01/2006 12/03/2006 400

1 12/03/2006 12/06/2006 500

1 12/06/2006 12/09/2006 600

I'm using a lookup transformation and modifying the SQL instruction in advanced tab like this:

select *
from
(select * from [dbo].[Price]) as refTable
where [refTable].[ID] = ?
and (? between [refTable].[BEGINNING_PRICE_DATE] and [refTable].[ENDING_PRICE_DATE]
or ? between [refTable].[BEGINNING_PRICE_DATE] and [refTable].[ENDING_PRICE_DATE]
or ([refTable].[BEGINNING_PRICE_DATE] between ? and ?
and [refTable].[ENDING_PRICE_DATE] between ? and ?))

In that case I have to define 7 parameters:

ID : parameter0

BEGINNING_DATE : parameter1, parameter3, parameter5

ENDING_DATE : parameter2, parameter4, parameter6

My problem is that the transformation looks for only one matching element and outputs 0 or 1 row per input row... In that case the 3 rows of my lookup table are matching with the row in my input table, but I have only the first one.

How could I do to resolve my problem?

Thanks... I hope it's clear...

Arnaud.

The lookup transformation can only output 1 row per 1 input row.

You would probably be best to load the data into a table and then join the 2 tables together.

You can simplyfy the join to

import.startDate < lookup.enddate

and import.endDate > lookup.startDate

This will match all records that overlap

|||

I think I have to keep my join because with your constraints I only have the price periods included in the imported periods and not those that overlap...

On the other hand I think I will create a temporary table for the input flow and join it to the lookup table as you recommend.

I wanted to avoid creating temporary tables but it seems to be the best solution here

Thanks a lot!

Arnaud.

|||

The conditions do allow for overlap.

In english they A and B overlap if A finshes after B starts and also A must start before B finishes. Thus the 2 criteria I posted.

|||

Yes Simon you're right!

My eyes were closed, sorry...

It simplyfies my sql request and all works fine, thanks a lot!

Arnaud.

|||No problem, Its a common scenario that many don't believe can be simplified to a single AND statement