How To Import SharePoint List Data Into A Database Table Using SSIS

For a variety of reasons you might need to import data that currently resides in a SharePoint list into a relational database table. Lucky for us there are some SharePoint Web Services we can leverage as data sources. This example will walk through pulling some example data from a SharePoint list I created and storing it in an SQL Server Table.

This walkthrough assumes you have some hands on experience/ knowledge of SQL Server Integration Services (SSIS).

In order to import our SharePoint list data we're going to leverage the "SharePoint List Adapters" for SSIS. You'll need to download them at http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652. Once you've installed "SharePointListAdaptersSetup.msi" you'll probably need to add the new source and destination objects to your toolbox. Start off by making sure you're on the "Data Flow Tab" and then right click the "Choose Items..." option as shown below.

clip_image001[4]

In the new window that comes up, you'll want to select the "SSIS Data Flow Items" tab, and then check "SharePoint List Source" then click OK.

clip_image003[4]

Now that we've added our connection objects to the Toolbox, all we need to do is drag the "SharePoint List Source" object onto our Data Flow canvas and then configure it to point to our list.

clip_image004[4]

Double click on the "SharePoint List Source" object. You'll want to configure the "Site URL" and "Site List Name" of the SharePoint list you're wanting to import (shown below highlighted in yellow).

clip_image006[4]

That's pretty much it. You'll probably also need to convert your column values from Unicode using the SSIS conversion transformation object before directing them to your destination data source. Overall though, pretty simple if you're familiar with SSIS. A quick proof of concept using a list of video games I made worked perfectly the first time.

From SharePoint List:

clip_image007[4]

To database table:

clip_image008[4]

almost effortlessly. Hope this helps.

 

How To Create Optional Parameters In A Stored Procedure Using T-SQL

Optional parameters are a pretty critical part of stored procedure writing. You won't get too far into report writing before users start asking for filters on their reports. In general it's a good idea to send as little data "across the wire" as possible for performance reasons (although you can filter data sets in Reporting Services after the fact as well). So how do you add an optional parameter to a stored procedure? Well, let's set up some example data. I have a table called "VideoGames" that has the following data in it.

clip_image001

Now let's talk about our objective: We want to write a procedure that is capable of returning the "GameTitle" and "GameSystem" columns in our "VideoGame" table. We also want our procedure to have an optional parameter called @p_GameSystem that returns only matching results for a Game System if a value is passed into it. If nothing gets passed into @p_GameSystem we want no filter to be applied to our procedure.

Ok let's start with a simple procedure that does almost everything we want it to, but the @p_GameSystem is not optional, and must always passed in when calling the procedure.

CREATE PROCEDURE usp_OpParamExample

@p_GameSystem VARCHAR(50)

AS

BEGIN --proc

      SELECT

            vg.GameTitle,

            vg.GameSystem

      FROM

            VideoGames vg

      WHERE

            vg.GameSystem = @p_GameSystem      

END --proc 

The first thing we need to do in order to make our @p_GameSystem parameter optional is to give it a default value. We can accomplish that by simply putting an = NULL after its declaration like so:

@p_GameSystem VARCHAR(50) = NULL

The next thing well want to do is change the WHERE clause to behave differently based on the value of @p_GameSystem . To do that, we're going to change its comparison to the following:

      WHERE

            (@p_GameSystem IS NULL OR vg.GameSystem = @p_GameSystem)   

Let's examine how this statement will evaluate for each of its two possible scenarios:

1. Nothing is passed into @p_GameSystem when the procedure is called: Under this scenario the first part of our WHERE statement (@p_GameSystem IS NULL) will always evaluate to true. Because we used "OR" between our two conditionals, only one of the statements needs to be true in order for the row to be returned. So every row in our table will be returned.

2. Something is passed into @p_GameSystem when the procedure is called: Under this scenario, the first test (@p_GameSystem IS NULL) will always be false, however our second condition (vg.GameSystem = @p_GameSystem) will potentially be true, assuming we've passed in a value that exists in our table. Because we used "OR" between our two conditionals, only one of the statements needs to be true in order for the row to be returned. So only rows where (vg.GameSystem = @p_GameSystem) will be returned.

That's it! We now have a procedure that allows a parameter to be optionally passed in. The entire procedure would now look like this:

CREATE PROCEDURE usp_OpParamExample

@p_GameSystem VARCHAR(50) = NULL

AS

BEGIN --proc

      SELECT

            vg.GameTitle,

            vg.GameSystem

      FROM

            VideoGames vg

      WHERE

            (@p_GameSystem IS NULL OR vg.GameSystem = @p_GameSystem)   

END --proc 

Now there are a few other ways we could have implemented this. Two common alternates to the WHERE statement would be either using the ISNULL operator like so:

WHERE

      vg.GameSystem = ISNULL(@p_GameSystem, vg.GameSystem)

or using COALESCE like so:

WHERE

      vg.GameSystem = COALESCE(@p_GameSystem, vg.GameSystem)

While I must admit both of these alternatives are way easier to read, It's my experience that neither of them perform as well as the method we used in our example, particularly against large sets, or where we have multiple optional parameters in the same query. Hope this helps.