In a previous blog post we covered a simple data flow for working with incremental loads in SSIS. In that example we were only concerned with creating and updating records. However it is often the case you need to delete records within your incremental load process as well. In this blog post we will cover how this can be achieved very easily using Diff Detector found within SSIS Productivity Pack.
Diff Detector is a transformation component that compares two inputs in order to find the differences between them. It generates 4 outputs; new rows, changed rows, unchanged rows and deleted rows. Because of this it can be an incredibly useful tool when building incremental loads in SSIS.
To start off we’ll establish our connections to our source and target databases and create a new data flow. We’ll then configure two Premium ADO.NET Source components, one for our source system and the other for our target system. Within these components we can select the columns we will need and perform any necessarily filtering. I will note that while we are using Premium ADO.NET Source in this example, you may use any other source component to retrieve your data. Later we will be using Premium ADO.NET Destination to write to our target, you may use another destination of your choosing here so long as it has support for Insert/Create, Update, and Delete write actions available, the majority of our other destination components support these actions.
With our source components configured we will drag out the Diff Detector. First we will connect our source system to the component. Here a dialog box will appear and we can select if this is the New Data or the Old Data. As the source system, this represents our New Data - the data that we are using to update our target system.
We will then connect our target system to the Diff Detector. With the New Data input already specified it will automatically configure this input as the Old Data. With this configuration, the New Rows output from the Diff Detector will contain the data that is in our source/new data input but not in our target/old data input, whereas the Deleted Rows output will be the data found in our target/old data input that it did not find in our source/new data input. The Changed Rows output will be data found in both systems but has one or more field that do not match between the two inputs. The Unchanged Rows output will generate the data found in both inputs that remain identical. It will use our specified key field(s) in order to determine if the record is in both systems or not.
Now we can configure the Diff Detector to find these comparisons. We’ll start off by mapping the fields we wish to compare, any field from the target system that has the same name as a field from the source will already be mapped for us. If you do not wish to or need to compare those two fields you may unmap them. In our example we are comparing all fields which will be written to the target system downstream. Another method when using this component for incremental loads may be if you have a field keeping track of the modification date, you could compare that field and a key field as a more robust way to find the changes.
In my scenario I want all fields to be exact matches as shown above, however within the Diff Detector we have the option to perform fuzzy matching as well as some more advanced matching such as first name match (will match full first names with common nick names: William will match Bill) and address match (will match short forms and full words such as Ave will match with Avenue). We can also configured more advanced settings as needed.
Once we have the Diff Detector configured we can begin to configure our destination components. We will need 3 Premium ADO.NET Destinations; one for added rows, one for changed rows, and one for deleted rows. We will not need to configure an output for the unchanged rows as these rows are identical in both the source and destination so no updates are required.
For our Added Rows output we will configure this component using the Insert action and configure our mappings. For our Changed Rows output we will use the Update action, when we configure our column mappings we will also need to select a key field to indicate which field it will match the update on, this should be the same field we used as the key field in the Diff Detector.
Lastly, for Deleted Rows output we will configure this to perform a Delete action. You may notice for this output we have different fields available. This is because these are the rows that were not found in the source system, our Diff Detector component has retrieved these rows from our target system, as such, the fields available for mapping are those we specified in our 'Target (Old Data)' source component. For the delete action only we only need the key field(s) to be matched in order for the delete to occur so we can unmap any other fields. For this scenario I am deleting based off of the ContactID which is the same key field I used in the Diff Detector.
We now have our entire data flow configured and can execute this task.
Benefits of using Diff Detector for Incremental Loads in SSIS
By using Diff Detector and SSIS Productivity Pack we are eliminating several steps in our SSIS incremental load development. Without SSIS Productivity Pack a typically incremental load that includes insert, update, and delete may look something like below, utilizing staging tables and multiple lookups and conditional splits.
Using Diff Detector and Premium ADO.NET we do not need to use any staging tables to perform the update and delete actions. As well we are able to complete the incremental load within one data flow task that is much quicker to develop and process.
Incremental Load with Diff Detector vs CDC
This solution is best used in situations when CDC cannot be used. If CDC is available this will offer greater performance for your incremental load in SSIS. This is because Diff Detector performs a lookup each time it is run to check for changes between the two systems. Databases with CDC enabled will keep track of these changes and the CDC SSIS components will only retrieve the changed data.
You can still use the Premium ADO.NET Destination within your development to eliminate the need for a staging table when using CDC. This would follow very closely with what we accomplished above by placing Premium ADO.NET Destinations immediately following the CDC Splitter.
If your database does not support CDC the Diff Detector can be a great tool to consider for managing incremental loads in SSIS, particularly when it comes to handling rows that have been hard deleted in the source system. Other solutions include using Upsert as described in our earlier blog post for cases when records are not removed.
This solution requires SSIS Productivity Pack, you can click here to download the free developer's license to get started.