Blog on SQL Server, Business Intelligence and the Cloud

Bulk Deleting Data from Using SSIS and Task Factory

Written by Brian Knight | Feb 26, 2016

One of the most frustrating things about is the feeling of constantly looking over your shoulder to make sure you don't run out of space. Doing bulk deletion of records in to fix this issue is done 200 rows at a time and takes a lot of time to setup. While there are many tools out there to help with this issue, the tend to cost quite a bit. This blog and video shows you a quick 4 minute solution to remove tons of rows from SalesForce using a free tool in Visual Studio, Task Factory and SSIS.

To start, I created a new SSIS package after downloading and installing Task Factory. I created a new SalesForce connection manager below. It will use your normal SalesForce user name and password. The password uses your password followed by your security token, which you can get from SalesForce.

Once the connection manager is created, go into the data flow and drag the TF Source over. Select your connection and the object you want to delete from. In the below screenshot, I selected to purge from my EmailMessage object. When you select the object, the SOQL query will automatically populate for you in the text box. Check the Edit SalesForce Query to add the WHERE clause to pair down your object to the records you wish deleted. Once you're comfortable with what you're about to delete, remove all the columns except the ID column as shown below.

The ID column is all you're going to need going forward. Next, drag over the TF Destination and connect the line from the source to the destination. Point the destination to the connection and same object you used before as shown below. Once you select the Action as Delete, the rest of the destination will configure itself.  

That's all there is to configuration of your purge. All in all, it should take 2 or 3 minutes to configure. Once you're done, run the package and the IDs for the records you've selected will be sent to the destination for removal. Once they're removed, make sure you go to the company's recycle bin and clean house. The space won't truely free up until the recycle bin is cleaned.

The video below shows you the configuration as well. Sorry for the raspy voice. I just recovered from the flu and am still finishing the last bits of it.