When I figured out errors or other issues when it comes to IT, I often feel like I’ve just defeated the Matrix. Granted… these situations are often due to my own incorrect settings (okay… pretty much always), but let’s not split hairs. I beat the machine, and that’s what matters.
The Task Factory Dimension Merge SCD component is a lot of fun. It also has a lot of moving parts and can be a challenge when one is going through the initial setup process. This is just my basic test setup:
Many people I speak with who are just getting started with the component ask questions about performance or may experience some deadlocking/hanging issues with their destinations. Here are some best practices I would recommend when initially setting up this component.
When using the Dimension Merge SCD, it is always recommend to have the sources (both your Source and Dimension) sorted by the Business Key in the Advanced Properties.
Advanced Properties Part 1: Change the Source Output “IsSorted” to True
Advanced Properties Part 2: Set SortKeyPosition on the Business Key(s)
Select the records using a SQL Statement and sort by your Business keys with an ORDER BY clause, not by simply selecting the table.
Follow the same steps for your Dimension source component as well.
This is where we can experience hanging or deadlocking because this package, possibly in addition to other packages, are all writing to your Dimension at the same time.
In this example, I’m using both OLE DB Destinations and the Task Factory Upsert Destination. For the OLE DB Destinations, I’m going to turn off Table Locking. This is my OLE DB Destination for New rows:
For the Task Factory Upsert Destinations, I’m going to check Turn Off Internal Transactions (the same can be done with the Task Factory Update Batch Transform, for the record). This is my Upsert Destination for Updated SCD1 records:
We’re essentially doing the same thing with each destination component.
As a side note, I have seen a number of people using the OLE DB Command to perform Updates & Inserts. If you’re already using Task Factory – take advantage of the Upsert Destination. You already have access to it, as a colleague of mine always says: let it do what it does best.
These steps have helped just about everyone I've spoken with, so I hope they do the same for you.