Deleting Records from an External List

This is an example of using Data Sync to clean up your data by deleting records you have in a separate list or document.

In this example we have an Excel Spreadsheet that contains the Record ID numbers of the records we wish to delete from a SQL Table.

To setup a project that will delete the records listed in this project we need to use a Lookup function with the source and target mapped to the same data source. The idea here is that we want to return from Datasource A only the records we wish to keep.

First setup the project with the Source and Target mapped to the same Datasource. This will always do nothing since both sides are equal.

Next add a Lookup to the Key Column (ID) to match the ID values from the Excel Spreadsheet. Set the Data Type to match the Spreadsheet type and Target Column to be ID from the Spreadsheet.

Next add an Expression in the Source Filter to return only those records we wish to keep. i.e. the records that are not listed in the Excel Spreadsheet. We use a test for NULL so if the result is NULL we want to keep the record.

The Expression in the case is ISNULL(Lookup1_ID)

Now when we compare A->B the result is a DELETE set that matches the records in the Excel Spreadsheet.

This same procedure can be used with virtually any data source and the Delete list can come from many different sources such as a SQL Query, FetchXML Expression etc.