Using SSIS and Excel Services to Build a Lightweight Reporting Solution for SharePoint-Based Applications (Part 2)

Using SSIS and Excel Services to Build a Lightweight Reporting Solution for SharePoint-Based Applications (Part 2)

This is the second part of the blog series which goes through the steps of building a SSIS package to import data from the ‘Sports Court Log’ Sharepoint list to the reporting database built in the first part of this series.

Step 1: Create an Integration Services Project on BIDS.

clip_image002[8]

Step 2: Install Sharepoint List Source and Destination Adapter. SSIS does not come with an OOTB Sharepoint list source/Destination adapter, but you can find a very good one on Codeplex.

After you have downloaded and installed the adapter, you need add ‘Sharepoint List Destination’ item and ‘Sharepoint List Source’ item onto ‘Toolbox’. To do that, you need switch to ‘Data Flow’ designer first.

clip_image003

Then right-click blank space on the toolbox and select ‘Choose Items…’ from the pop up menu. Go to ‘SSIS Data Flow Items’ tab and select ‘Sharepoint List Destination’ item and ‘Sharepoint List Source’ item, and then click ‘OK’ button.

clip_image004

The ‘Sharepoint List Destination’ item and ‘Sharepoint List Source’ item will show up on the toolbox after you restart BIDS.

clip_image006[5]

Step 3: Add an ‘Execute SQL Task’ to control flow to remove existing records in ‘FactSportsCourtLog’ table and ‘DimTime’ table.

clip_image008[4]

clip_image009

Step 4: Add an ‘Execute SQL Task’ to populate ‘DimTime’ table.

clip_image011

clip_image012

Step 5: Add a ‘Data Flow Task’ to import data from ‘Sports Court Log’ Sharepoint list to the ‘FactSportsCourtLog’ in reporting database.

clip_image013

Step 6: Add and configure ‘Sharepoint List Source’ in ‘Import Demo Sports Court Log’ data flow task. Firstly, Double-click the ‘Import Demo Sports Court Log’ task and enter the ‘Data Flow’ designer. Then add a ‘Sharepoint List Source’ item to the data flow. The ‘Sharepoint List Source’ item will import the ‘Sports Court Log’ list from Sharepoint.

Then you need create a Sharepoint connection manager (‘SPCRED’ type) and use it in the ‘Sharepoint List Source’.

clip_image015

Also, you need configure the Sharepoint SiteUrl, SiteListName, and SiteListViewName(optional) to point the ‘Sharepoint List Soruce’ to the ‘Sports Court Log’ Sharepoint list (You can set those properties on the ‘Component Properties’ tab).

clip_image016

Step 7: Add a ‘Lookup’ task to the data flow to map the date in ‘FactSportsCourtLog’ table and ‘DimTime’ table through the ‘Timekey’ which will be saved to the ‘FactSportsCourtLog’ table as the foreign key to ‘DimTime’ table.

clip_image018

clip_image020

Step 8: Add a ‘Derived Column’ task to handle with ‘Empty’ input on ‘Sports Court Log’ Sharepoint list. You may need add more logics to clean your data depending on the complexity and quality of your dataset.

clip_image022[4]

Step 9: Add ‘OLE DB Destination’ and points it to the ‘FactSportsCourtLog’ table in the reporting database. Then you need set the mapping between the input columns from the data flow and the destination columns in the ‘FactSportsCourtLog’ table.

clip_image024[4]

Now, you have the ‘Import Demo Sports Court Log’ data flow setup.

clip_image025

You can give it a test run on BIDS, and you can see the data in the Sports Court Log’ Sharepoint list has been added into the ‘FactSportsCourtLog’ table;

clip_image026

clip_image028[4]

Step 10: The last step for building the SSIS package is to setup an alert Emailing task which will send an email to administrators if any error occurs when executing the SSIS package. The function is very important if you want your SSIS package executed automatically through timer jobs.

clip_image029[4]

This part of the blog series goes through the steps for building the SSIS package to transfer data from Sharepoint to reporting database. The next part of the blog series will introduce how to setup SQL Server Agent Job to periodically execute the SSIS package.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s