• Part 1 – Introduction & Building the Back-End Reporting Database
• Part 2 – Developing SSIS Package to Import Data from Sharepoint Lists to Reporting Database
• Part 3 – Setup SQL Server Agent Job to periodically execute the SSIS Package
• Part 4 – Building and Publish Excel Reports on Sharepoint

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.

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.

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.

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

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

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

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

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’.

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).

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.

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.

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.

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

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;

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.

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.

• SSIS/SSAS/SSRS/MDX