With Task Factory 2019.4.2, the SharePoint Destination can delete documents from a SharePoint documents list. To do so, the SharePoint SOAP API requires the ID and full URL path to the document(s) you want to delete. Due to API limitations, the Task Factory SharePoint Source does not return each document’s fully qualified URL, which is needed to perform the action in the SharePoint Destination. However, this issue can be quickly remedied by adding a few simple steps to your SQL Server Integration Services (SSIS) package configuration.
Retrieving Each Document’s URL from SharePoint
To begin, you need to capture each file’s URL path to build the full URL string required in the SharePoint Destination. Start by adding a SharePoint Source to your data flow, open the SharePoint Source, and create/select a connection manager to your organization’s SharePoint site. Next, select the documents list that contains the file(s) you want to delete. (Note that you can select an optional view or add filters to help target specific files.) Select the Include Hidden Columns and Remove ID’s From Results options, as shown in the screenshot below, to complete the source configuration.
Select the Include Hidden Columns and Remove ID’s From Results options in the SharePoint Source
Build the Full URL Using an Advanced Derived Column
Add, connect, and open an Advanced Derived Column after closing the SharePoint Source. Then, add a new column, name it, and click the Expression ellipsis (shown in the screenshot below) to open the Expression Editor.
The Expression ellipsis to open the Expression Editor
Before you can build the expression that will create each document’s full URL, you need to grab the organization’s SharePoint base URL. Open a web browser and copy the organization’s SharePoint URL (example shown in the screenshot below) from the address bar.
Example SharePoint base URL
Return to the Advanced Derived Column’s Expression Editor. In the Expression window, paste the URL copied from the web browser and surround it with single quotation marks. Also, append + [URL Path] to the end of the expression, as shown in the screenshot below. Note that you can find the URL Path column in the Columns/Variables list located in the left pane. You can drag and drop the URL Path column into the Expression window or type it in. If you are unable to locate URL Path in the Columns/Variables list, please return to the SharePoint Source and ensure the Include Hidden Columns option is selected.
Appending the URL path to the end of the expression
The expression is now set. You can close the Expression Editor and the Advanced Derived Column by clicking the OK button in each window.
Configure the SharePoint Destination
You now have all the necessary data needed to configure the SharePoint Destination to perform a delete action. Add a SharePoint Destination to the data flow, connect it to the Advanced Derived Column, and open the SharePoint Destination. Next, select/create a connection manager, point to the documents list, and set the Action to Delete. Finally, map the ID and Name columns. In the example shown below, we have mapped the SharePoint Source’s ID column to the ID destination column and the GeneratedURL column created in the Advanced Derived Column to the destination’s Name column. All other mapped columns should be removed by selecting Ignore from the drop-down menu. Click OK to close the SharePoint Destination.
Mapping the ID and Name columns
Adjust the Name Column Description in the Advanced Editor
The last step is to right click the SharePoint Destination and select Show Advanced Editor. Expand the SharePoint Destination Input’s External Columns list and click the Name column. (Note that there can be multiple Name columns, so be sure the description in the Common Properties window says FileLeafRef.) Then, change the description from FileLeafRef to FileRef, as shown in the following screenshot.
Updating the Name column description
And, that’s it! Click OK to close the Advanced Editor and execute the package. As you can see, building a SharePoint Destination package to delete documents can be done quickly and easily with just a few extra, but simple steps.
An example of this package can be downloaded by clicking here. You can also find a similar package in the local Documents/SentryOne/TaskFactory_Sample_Packages directory after installing Task Factory 2019.4.2.
Be sure to check out our other high-performance Task Factory components designed to accelerate your ETL processes.
