Friday, 11 May 2012

Incremental Load (ETL) with SSIS, SharePoint, and SQL

Introduction

There are many way to for processing incremental data in SSIS. Look ups and CDC transformations are the obvious options, but  they cannot be used when working with a SharePoint list.

Scenario
 
The requirement mandated that only list item that have been "Approved" should be shifted into a SQL Database. The SSIS package will be run multiple times a day.
 

Ingredients: 

  1. Download and Install SharePoint Source and Destination List Adapters
  2. One SharePoint List with three different views. After your list is made add an extra Yes/No Column named it "isApproved". We will be updating this column via the SSIS package for records that have been approved.
    1. Not Approved (Filter for records which have not been approved)
    2. Approved (Filter for records which have been approved and are tagged as "No" in the is Approved Column"
    3. isApproved (Filter for records which are tagged as "Yes" in the isApproved column.


The goal of this process is to transfer records in to SQL Server without violating the primary key.

Step 1: Open the SQL Server Data Tools from the start menu and create a new Integration project.

(SSDT is a feature of SQL Server 2012, it was formerly known as the Business Intelligence   Development Studio)

If you are working on BIDS instead of SSDT you will have to manually add the SP List Adapters to the project SSIS Toolbox. SSDT will recognize the newly installed adapters and add them to the SSIS Tool Box automatically.


 
Step 2

Click on the control flow tab and drag a Data Flow Task onto the designer, rename it Transfer to SQL.


Double click on the "Transfer into SQL" data flow task to enter it and design the data flow.


In the Data Flow tab drag the SharePoint List Source Adapter onto the Designer.



Right click in the "Connection Mangers window and select create new connection. Select the SPCred connection to connect authenticate the share point website.


Double Click on the SharePoint List Source to set its properties.

  1. Set the credentials to SPCred you set in the SSIS Connection Manager.
  2. In the Component Tab set the properties for the following fields.
    1. SiteListName - Your list source name
    2. SiteListViewName - Approved
    3. SiteUrl - The URL of the your SharePoint Site where your list is located.


Click OK

Once that is done drag an "ADO.net" Destination from the SSIS Toolbox and connect the SP List Source to the ADO.net Destination. (Drag the arrow from the source to the Destination)

In the SharePoint Connection Manager add a new connection to the database and table you want to transfer your list into. Double click on the ADO.net Destination and set the connection manager.

In the mappings tab map your input columns to your output columns and press okay.



This is the end of the first half of the SSIS Package.

Updating the List (Second Data Flow)

In the previous Data Flow we shifted the approved records from the SharePoint List to a SQL Database.

Now what we will do is create a second Data Flow and name it "Update List".


Double click on the Data Flow and add the Following to the design surface.

  1. SharePoint List Source
  2. Derived Column 
  3. SharePoint List Destination
 Click on the List Source to set the Properties


Note - If your remember from the previous tutorial, after List Item is Approved it get shifted to "Approved List View".

After that connect an arrow to the Derived Column Transformation. 


Note - Once the items have been moved into SQL, we will be updating a YES/NO column in the SharePoint List. This will be accomplished by using the Derived Column Transformation.

Once the Item has been approved, it will be tagged as YES in the isApproved column and then shifted to the "isApproved" List View.

Finally connect the Derived Column to the SharePoint Destination and set the following properties for the Destination.


Finally Your Flow for the Data Flow Package should look like this


 

1 comment:

  1. Will this work with SharePoint 2013?

    Do I need SQL 2012 or 2014?

    ReplyDelete