Phil Parkin (11/27/2012)

---

rhythm.varshney (11/27/2012)

---

Phil Parkin (11/27/2012)

---

There is no good way of doing this in SSIS - I'm with Jeff on this one.

If you want to put an SSIS 'wrapper' round it, something like this would work:

1) Exec SQL: Truncate Staging table

2) Dataflow: Insert to Staging table

3) Exec SQL: UPSERT logic to move from Staging to main data table

Thanks Phil.

However I went through some posts where they have suggested look up transformation for this.

But not sure if it would not hit the performance in future.

The cached and partially cached lookups won't work because you have duplicates (by name) in your source data. Maybe the uncached lookup would work, but it is performance suicide when compared with what the SQL Server engine can give you.