We are running a ticketsystem on Sharepoint 2010 which got alarmingly large over the last few months. The ListView Threshhold currently sits at 15k for this WebApplication.

I am currently looking for a suitable way to remove old items from that list and write them into a table on our SQL server.

So my plan would be something along those lines:

  • eventhandler gets fired daily/weekly
  • check list for items older then 3 months
  • check if items have status 'implemented'
  • put items into datatable
  • delete items from List 'Tickets'
  • load items into SQL server table.

Is there any flaw in my bulletlist above? I am looking for resources on any of the above steps or even better ways to implement this.

Kind regards


I would use SQL Server Integration Services to accomplish this, using the SharePoint list adapters from here:

SharePoint SSIS List Adapters - codeplex

SSIS Connections - Microsoft

I have used these and they work well. You would connect to the list as the input data source, and use the SSIS tools to loop through and check each list item and then act on the information from the list item (i.e. skipping it, or writing to a database table and deleting the list item).

You can schedule SSIS packages to run periodically in a variety of ways, using SQL Server Agent, or using the SSIS command line (dtexec) with the regular Windows scheduler.

You also could write code against the the object model that loops through all the items in the list, checks them, and outputs as required to a database, and you also could use Powershell scripting with the SharePoint and SQL Server snap-ins to accomplish this. You would then schedule the command-line program or powershell script to run periodically.

  • CodePlex is now archived. I downloaded the archive, but can't seem to get SharePoint as a Source or Destination in my SSIS Toolbox (VS 2013 Pro). Any suggestions? – Gary Aug 21 '18 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.