Delete DOCUMENTS vs ITEMS - unable to delete documents with SharePoint List Destination

Jun 9, 2015 at 2:24 AM
Hi guys,

I am in a Server 2012 environment, trying to get documents from a SP list and then delete the items.
I have followed the standard approach of configuring a list source (which pulls the documents without issues). I am mapping this to a destination with the batch type set to deletion.
From most of the blogposts I've seen about this component, all I have to supply is ID.

http://dataqueen.unlimitedviz.com/2011/06/how-to-use-a-sharepoint-list-as-a-data-source-in-your-ssis-package/

However, using fiddler, I saw that it failed to delete every item I was pulling back.

From fiddler, I noticed that this is simply using ListItemUpdate.asmx service underneath. I did a search on what is required in the xml for this operation, and msdn says it requires ID AND FileRef for deleting documents (maybe different for simple items).
(scroll down to "delete documents")
https://msdn.microsoft.com/en-us/library/lists.lists.updatelistitems(v=office.12).aspx

I then tried to pull in the hidden and lookup fields in the list source.
I then realised that the destination columns does not contain FileRef in the SP List Destination component. So I tried adding a column called FilRef, but the designer did not like this as it gave me an error, preventing me from saving my changes to the destination component.

Can anyone please let me know if there is a known issue with deleting documents vs items, and if there is a workaround for this?

Cheers,
Lochana