Using SSIS for Bulk Data Deletion in Dynamics 365

ByRob Peledie

Using SSIS for Bulk Data Deletion in Dynamics 365

I had a situation recently that an instance (not one I managed I hasten to add!) had started to grow in storage, to the point that it was at nearly 200% capacity. I was asked to look at it. Turned out that Plugin Tracing had been switched on, and a plugin was throwing errors. Unfortunately there were now so many records in the Log file that it wouldn’t open (Usual generic unhelpful SQL Error).

Taking a quick look at organisation insights confirmed that this was consuming nearly 60gb of storage, and a quick record count (Using Record Counter in XrmToolBox) indication there was approaching 10,000,000 records.

Normally I would use the standard Bulk Delete option, however for a couple of reasons I didn’t think this was a viable option:

  1. It would take too long
  2. It kept falling over after 1000 deletes (probably due to the number of records, but I didn’t get to the bottom of it)

In the end I decided to go back to trusty old SSIS, and (again) the lovely KingswaySoft solution for Dynamics 365.

The steps are essentially:

  1. Create a CRM connection
  2. Retrieve what you want to delete (Using Dynamics Data Source Component)
  3. Delete

If you’ve used the SSIS Toolbox before, then you’ll know how to setup a connection.

To retrieve the data, I decided to go with FetchXML as I wanted to specify a date range, so the following was added to the Dynamics Source Data Flow:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="plugintracelog">
    <attribute name="plugintracelogid" />
    <filter type="and">
      <condition attribute="createdon" operator="on-or-before" value="2018-11-20" />
    </filter>
  </entity>
</fetch>

So the Fetch above would pull back any log files created before 20/11/2018.

Once this is done, connect the Source to the destination, and open up the Destination Module

Add your connection in Connection Manager.
In Action, choose Delete.
In Destination Entity, choose the Plugintracelog entity

I enabled multithread, and chose 32 threads.

Once that is all done, Save, Build and Run.

About the author

Rob Peledie administrator

3 Comments so far

Hosk’s recommended Dynamics 365 and other articles November 2018 – Hosk's Dynamic CRM BlogPosted on9:42 am - Dec 12, 2018

[…] Using SSIS for Bulk Data Deletion in Dynamics 365 […]

Hosk’s recommended Dynamics 365 and other articles November 2018 – Microsoft Dynamics CRM CommunityPosted on10:31 am - Dec 12, 2018

[…] Using SSIS for Bulk Data Deletion in Dynamics 365 […]

Using SSIS for Bulk Data Deletion in Dynamics 365 – 365Knowledge – Dynamics365BlogsPosted on12:37 pm - Feb 13, 2019

[…] Using SSIS for Bulk Data Deletion in Dynamics 365 […]