CODEWITHSUNDEEP
sql-serversql-server-2008ssis
jesvin
jesvin

Reputation: 65

SSIS – Locking in Target table when SCD is implemented using Lookup

To override the in-build SCD transformation in SSIS dataflow, I used checksum values of columns and a lookup. Below is the process.

I need to implement the SCD type 1 in Target_Fact_table.

Source Query

Select key, a, b, CHECKSUM (a,b) new_value from Source_table

In Lookup 

Select  key, CHECKSUM (a,b) Old_value from Target_Fact_table

If no match found the record will be inserted and if match found compare the New_value and Old_value and if any change then update the record.

First run doesn’t have any issue. But for the second time when source having more records to update and insert then the target table will be locked because of the bulk insert and update.

I tried removing the table lock from the OLE DB Destination task but still the locking is there.

What can I do to avoid this locking or can i put some small delay in the update transformation ?

Upvotes: 0

Views: 738

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

Your optimal solution is instead of an OLEDB Command to update the matched values, insert your matched data into a staging table in the destination and then do a single UPDATE JOIN statement on the fact table and your staging table to update all the new values.

This avoids locking, increases throughput, and can provide a cleaner audit trail for changes.

Upvotes: 2

Related Questions