How to setup a retention policy based on data from an external table
Summary
WARNING, THIS ARTICLE DESCRIBES HOW TO DELETE DATA, INCORRECT CONFIGURATION MAY LEAD TO UNINTENDED DATA DELETION. YOU SHOULD ONLY ATTEMPT THIS IF YOU HAVE HAD TRAINING AND ARE CONFIDENT WITH DATA DEFINITION MANAGEMENT, ALTERNATIVELY OUR SERVICES TEAM CAN ASSIST WITH THE CONFIGURATION.
If you are storing documents in DataStoreDSX and wish to remove them in bulk based on data stored in an external table, then here are some instructions on how to do this.
Note: This example is using account number field to delete statements stored in DSX where the status is set to DELETE.
Steps
1) Create a SQL Database on the same SQL Instance as the DataStore database. The collation of this database must be Latin1_General_CI_AI and the table must have a field called PK_IDRow with unique values, in addition to an Account Number field and a Status_Flag field. I named this database as Retention.
2) In Management Studio, create a Custom Field Type for Status field (as Text)
Note: After creating the Custom Field Type. Close and reopen Management Studio
3) In Management Studio, create a new Category called Retention and add the Data Definition you wish to delete documents from
4) Using this Category, add a Custom Security and configure the Database and Table that has been created in SQL Server.
5) The Joining Field should be Account Number which maps to the Account_Number column in the Retention database
6) Map the Status Field to the Status_Flag field in the Retention Database
7) Save Changes
8) Create a new Search Template, add a new field called Account Number and link it to the Account Number in the Data Definition
9) Create a Search Assistant field
10) Setup a Microsoft SQL Data Provider to the external Retention database. Use the Account Number field as the joining field and map the Search Assistant field to the Status_Flag field. Save the Search Template
11) Setup a new Retention Policy in Management Studio, choose the Search Template that you just created.
12) Enter * in the Account_Number field and the word DELETE in the Retention Field. This will delete all account numbers that have the value of DELETE in the Status_Flag field in the Retention Database
13) Set the When to remove the data to 0 days after Item Stored Date and Time, alternatively you can set it to be x number of days after a document date.
14) Setup a Retention Policy Schedule to determine when the retention policy should run
15) A successful deletion should show Event ID 123 and 212 in Event Viewer
