PART 1 – DOWNLOAD AND INSTALL SSIS TOOLBOX ITEMS
1. From the server where you will be building the SSIS solution, open your browser and navigate to this URL. http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652
2. Download, run and follow the instructions to install SharePointListAdaptersSetup.msi (2008) or SharePointListAdaptersSetupForSqlServer2005.msi
3. Close BIDS if it is open
4. Open BIDS from the Tools menu select Choose Toolbox Items. Go to the SSIS Data Flow Items tab. – **See SQL Server 2012 Update at the end of this post.
5. Add a checkmark to the SharePoint List Destination and SharePoint List Source items.
6. Open up or create a new Integration Services solution. Go to the Data Flow tab.
7. Open up the Toolbox
8. Drag SharePoint List Source from the General section…
…to the Data Flow Sources section.
9. Drag SharePoint List Destination from the General section…
…to the Data Flow Destinations section.
Now you are ready to use a SharePoint List as a source or destination in your packages.
PART 2 – USE THE SHAREPOINT LIST AS A DATA SOURCE
1. Create a Data Flow in your package.
2. Go to the Data Flow tab and drag the SharePoint List Source into the data flow as your source.
3. Double click on the source. Scroll down to the bottom where it says SiteURL.
4. Paste the URL for the SharePoint site into the SiteURL (exclude anything from the word Lists onward in the URL)
Example: if your SharePoint site URL for Lists is
http://ca-shpt01/Reporting/Lists/Department%20Groups/AllItems.aspx
then only type
http://ca-shpt01/Reporting/
5. Type the SiteListName. For example if your List name is Departments,
then type “Departments”.
6. If you are wanting to import data from a particular view of this list in SharePoint, other than the default view, “MyView” for example:
then add the name of the view in the SiteListViewName, otherwise leave it blank.
7. Click the Refresh button at the bottom of the box to load the SharePoint List metadata into your Data Flow.
8. Then click on the Column Mappings tab to see the columns available from your SharePoint list.
9. Drag a Data Flow Destination onto your data flow and proceed as normal to pull data into your destination from the SharePoint list.
You’ll need to know a little about SharePoint lists to understand what data lives in which column. You’ll need the ID column if you will be doing any updating from the data warehouse into the SharePoint . The Title is whatever item is the linked
item in the SharePoint list – meaning if a user clicks on one of the items in the SharePoint list, which field is set up as the hyperlink to the item (usually the first column visible in the list).
Because there is often a push and a pull of data to and from the
sharepoint 2013 to sharepoint online migration. I tend to create a table in the data warehouse specifically for the data from the SharePoint list and I let the source data dictate the table structure by clicking on the New button for the “Name of
the Table or view”. In this way, when the data gets pushed back to SharePoint there are no data type issues.
