The Change Filter component allows you to determine which rows or records in a SQL Server Integration Services data-flow have been inserted, deleted or updated over time.  Essentially it maintains a state database (called a Delta database), and routes all incoming rows or records to one of four outputs depending on whether it is changed, new, deleted, or unchanged relative to the last synchronization point.  This is incredibly useful for batch scenarios, enabling you to efficiently determine all changes to a view of data since last synchronization.

The component can be configured to automatically synchronize on successful completion of the data-flow, or this can be controlled manually via the Commit Change Filter Component.

It can sometimes be useful for multiple Change Filter components to share a Delta database, provided they are operating on the same schema and with the same field settings, and this can provide a useful and efficient way to enumerate the differences between two tables or views. 

To configure a Change Filter component, first drag it onto the data-flow surface, and link to it.  Then double-click to configure. 



The first page presented allows you to configure the Delta database.  You can also optionally specify a Batch identifier, which can be used to partition records within a Delta database, allowing multiple Change Filter components to share the same database without interfering with each other, and you can specify whether successful completion of the data-flow triggers a synchronization point (such that the new data becomes the current state).

The next page allow you to select which fields to include as part of the Primary Key.  The combination of fields selected for the Primary Key must uniquely identify a record.  You must select at least one field.



The next page allow you to select which fields to monitor for changes. Changes to fields that are not selected in this list will result in the record being flagged as Unchanged.



The next page allow you to select which fields you want to retain the previous value for. If you select a field in this list, an associated column with the postfix _OldValue will be created in the Changed Rows output, and you'll be able to use this to access the previous value of the field prior to the change.



Finally the Deleted record fields page allows you to select the fields that you would like to retain in relation to deleted records.  These columns will be present in the Deleted Rows output.

Using the Component

Having configured the component, simply drag an output from it, and select from New Rows, Changed Rows, Deleted Rows and Unchanged Rows.  You can then perform case-specific processing as required, and even access old values.  It's as simple as that!  Note that if you didn't check the 'Automatically configure changes' option then you'll need to use a Commit Change Filter Component to commit the changes to the database, otherwise you'll get the same result every time you run as the Delta database won't be updated.



All BlueSSIS data-flow components support properties 'Fail on truncation' and 'Unicode mode', allowing you to define applicable truncation and Unicode settings. These properties can be found in the Visual Studio/BIDS Properties window as shown below :