We’re starting a new project where we’re collecting data, amongst others, from SharePoint lists from the Municipality’s site. Though the SharePoint developers recommended we import data through a web service, we found that SSIS has a component for SharePoint as a data source. We started off by downloading and installing the SSIS component for a SharePoint List Source from CodePlex as it does not come with the SSIS itself. We added the component to our SSIS through the Tools menu > Choose toolbox items and then chose the relevant components:
I needed to predefine for our DBA the tables we needed to build based on those SharePoint lists. You can add a data flow destination to that SharePoint list source and double click on the Data Flow Path Editor:
Click on the Metadata tab and hit the “Copy to Clipboard” button and paste it to an Excel file so you can see the name of the columns in that SharePoint list and their SQL data type for your future DWH tables. For any further data on using SharePoint as a source or a destination to update, I really recommend you read the very comprehensive document on extracting and loading SharePoint data in SQL Server Integration Services.