Need Help With SSIS Logic

[Fsb]

Hello,

have a source table:

Business Key
Value

1
A

2
B

3
C




Here is my destination table:

Business Key
Value
Active Flag
Import Date
Modified by

1
L
1
1/1/1
Test user

2
B
1
1/1/1
Test user

5
D
1
1/1/1
Test user




I need to update the destination table in three passes:

1. Update new records (i.e Business key 1 Value will be update from 1
to A

2. Add new (I.e. Business key 3 will be added)

3. deactivate records that do not match the source (i.e 5 Active Flag
would be set to 0)

4. If the source and destination match (based on key and Value) to
nothing.

[Todd C]

This is a classic scenario, one that cannot be done within one Data Flow. You
need to break it up into at least two or three.

First, have two Sources (one is your 'source' table, other is your
'destination' table). Bring the two together in an INNER Merge Join
transform. From there, go to an OLE DB Command and have it update the
appropriate column in the destination table based on the fact that the
Business Keys are equal and the Value fields are not. (May need a conditional
split to jsut grab rows where "Value1 <> Value2")

Second, do the same two sources, but have a LEFT Outer Join in the Merge,
and this is the inserts.

Thirdly, do it again, but reverse the OUTER Join to the other side, and this
becomes your records to flag as in-active using another OLE DB Command.

FYI: If you have a large set of data to run through this, OLE DB COmmands
executing for each and every row of a data flow is *very* inefficient. You
may want to consider creating a staging table in the database then handle all
the INSERTS, UPDATES, and DELETES in a couple statements in a stored
procedure.

HTH
=====
Todd C

Post by Fsb
Hello,
Business Key
Value
1
A
2
B
3
C
Business Key
Value
Active Flag
Import Date
Modified by
1
L
1
1/1/1
Test user
2
B
1
1/1/1
Test user
5
D
1
1/1/1
Test user
1. Update new records (i.e Business key 1 Value will be update from 1
to A
2. Add new (I.e. Business key 3 will be added)
3. deactivate records that do not match the source (i.e 5 Active Flag
would be set to 0)
4. If the source and destination match (based on key and Value) to
nothing.