Not deleting list items and corrupts list

Aug 12, 2010 at 1:03 AM

We seem to be having strange issues with items NOT being deleted from the list however the package said it did delete and over time the list grows and grows. Not sure what is causing this issue because the SSIS packages are running successful. The site does not have a recycle bin (its disabled) and I found that recreating the list from a template seems to resolve for the first couple of loads but will eventually contain duplicates after a matter of days.

The data flow is delete all items in list ~4,000 of them, then reload with new data. The package is saying it successfully deleted 4,000 rows but after the next load it now has 8,000 and continues throughout the day.

The recycle bin is off
There is no comparison from before/after it simply deletes items and inserts new.
We just upgraded to SSIS 2008 from 2005 thinking that would resolve the issue but it has not.

I haven't noticed or experienced the failure to delete issue with lists fewer than 1,000 items. Both problematic lists have 1,900 and 4,000 items. Unfortunately change detection is really not an option for us...

Has anyone experienced this before? Any help would be appreciated!

Aug 13, 2010 at 7:05 PM

For some reason adding a CAML query to the delete process seemed to pick up ALL of the list items. We have been using these adaptors for over 2 years with WSS3.0 and never had the need to add a query in the SSIS Source/Destination delete routine.

Adding a simple query where ID > 0 seemed to pick up all items.

<Query><Where><Gt><FieldRef Name="ID" /><Value Type="Counter">0</Value></Gt></Where></Query>

For some reason if a query was not specificed the adaptor ONLY deleted the last batch of items loaded into it ~4,000. The result would be 4,000 items deleted and 4,000 items loaded however viewing the list showed 16,000 items. Items were still in the list from previous loads? It is very strange but if this holds up I'm happy with this as a resolution.

Platform: Server 2008 R2 x64, SQL 2008 R2 x64, WSS3.0