Can This Be Possible With Out SCD?

knip1 · Post by **knip1** » Mon Jul 02, 2012 9:35 pm

Hi Experts,
Requirement:
i want to update a record in the table based on the record from the source.
my table basically has 3 columns
identity,datestart,dateend,flag
1 2010-01-31 9999-12-31 M
if iam getting a record frm file say
identity datestart flag
1 2010-02-28 T
Then the record in the table should be
Identifier,datestart,datend,flag
1 2010-01-31 2010-02-28 M
1 2010-02-28 9999-12-31 T

can i impement this with out using SCD?

ray.wurlod · Post by **ray.wurlod** » Mon Jul 02, 2012 11:17 pm

Yes, basically using Lookup and Transformer stages. The logic is contained within the Transformer stage.

knip1 · Post by **knip1** » Tue Jul 03, 2012 12:35 am

Ray Thanks For Reply,
Can you please elaborate on the logic

ray.wurlod · Post by **ray.wurlod** » Tue Jul 03, 2012 3:39 am

Set the Lookup Failed rule to Continue.
In the Transformer stage detect success or failure of the lookup with an IsNull() function.
If the lookup fails, your new record goes to an Insert link.
If the lookup succeeds, your new record goes to an Insert link and the columns returned from the Lookup go to an Update link, with appropriate column derivation expressions.