Use Integration Services to Import SharePoint List Items to SQL Server

A recent project I worked on called for data captured into a SharePoint list using InfoPath Forms Services to be ultimately imported into a SQL Server database for use with a set of performance reports. 

Given that InfoPath solutions can be designed to submit data to SQL Server (generally via a web service) you may ask why this wasn’t used for the solution.  Well, we wanted to do more stuff with the captured data in SharePoint, using workflows and task assignments before the data finally got approved and loaded into SQL Server.

In summary, the data was going to be submitted using InfoPath and Forms Services as promoted columns in a document library.  Once the appropriate people had reviewed, actioned and submitted the data it would be moved to a separate list, and any new items then needed to be loaded into SQL Server.  From there, Reporting Services would be used to generate and distribute nice looking reports.

So I took a look around to see what tools I might use to get the data into SQL Server with minimum coding effort, and ideally without installing any custom components on the SharePoint server.  I was aware of a set of SharePoint Designer Actions on CodePlex which included an Execute SQL Action.  For me the problem with this was that I was fairly sure I wouldn’t be allowed to install these on the production SharePoint server.

Also on CodePlex is a set of SQL Server Integration Services Community Samples, including SharePoint List Source and Destination Components.  For this project these components seemed more suitable, particularly as we would only need to import new items into SQL Server at the end of each day, and Integration Services would let us schedule a daily import.  And there’s even a MSDN article showing you how to do it.

To show how it works, I created a test SharePoint list called Responses which looks like this (let’s assume this data is coming in via InfoPath)

And on SQL Server, I created a table which looks like this:

Once the List Source and Destination components have been installed into Integration Services, I can create a Data Flow Task to read from SharePoint and write to SQL Server:

And map columns like so -

Of course when I run this I only want to import new rows.  The way I’ve handled this is to store the ID of the SharePoint list items in the SQL Server table and execute a SQL task to get the Max ID currently in the table like so –

SELECT ISNULL(MAX(ResponseID),0) AS LastResponseID  FROM Responses

As the SharePoint List Source component has a CAMLQuery property which I can update with a property expression at runtime, I can set it to something like this –

<Query><Where><Gt><FieldRef Name="ID" /><Value Type="Counter">1</Value></Gt></Where></Query>

In fact in the Integration Services Property Expression Editor I created an expression like this (LastResponseID is the output from my SQL Query):

"<Query><Where><Gt><FieldRef Name=’ID’ /><Value Type=’Counter’>" + (DT_STR, 4, 1252)  @[User::LastResponseID] +"</Value></Gt></Where></Query>"

So now if I put all of this together, I have an Integration Services Package which -

- Gets the last imported ID from the SQL Server table

- Queries the SharePoint list for items with an ID greater than the last one imported

- Loads any new list items into the table

Making it easy to get my list items -

Into my SQL Server table -

And I can schedule this to run each night, all without installing any new stuff on the SharePoint server or spending any money on new components!

Technorati tags: SharePoint, Integration Services

SharePoint in Plain English

I know there are already quite a few SharePoint-related blogs mentioning this video, but for any WorkerThread clients who haven’t seen it yet, it’s a nice little overview of what SharePoint is about and very useful when you are talking anyone who still doesn’t get it -

WordPress won’t seem to let me embed the video I’m afraid, so go and spend 3 minutes taking a look at SharePoint in Plain English.

And here’s another one on how to put the Share into SharePoint.

Update – Sharon at Joining Dots found the video on YouTube so I can embed it now:

Technorati tags: SharePoint, video

Checking Your SQL Server Jobs

Far too frequently I go to a client site, take a look at their SQL Server setup and notice that one or more of their SQL Server scheduled jobs is failing.  Now we all know that job monitoring should be part of a DBA’s daily checklist, but still things seem to get missed, particularly if there are multiple servers to monitor, a stream of user departments asking for assistance, etc etc.   Not good, especially if the job is a critical operation and you only find out when you need to do a restore.

So I often suggest that they download SQL Job Manager from Idera.  This is a free tool which lets you set up a single view of all jobs running on your SQL Server installations and very quickly see/fix any failure or contention problems.  Well worth a look.

And if you are doing SharePoint admin, they have a free SharePoint Performance Monitor as well.

Technorati tags: SQL Server, backup, administration, monitor

Dashboards, Data, Sparklines and Excel 2010

I’ve been really busy over the past few weeks, so haven’t had much time for new blog posts.  So I thought I would roll up some related news into one post, as all of the information is about Business Intelligence, data and dashboards.

First, if you are interested in how browser-based dashboards can be used to show a company’s financial data, take a look at Microsoft Investor Central.  Here’s a snippet showing what it looks like right now:

If you visit the page you will see it makes heavy use of Silverlight to let you zoom in on and manipulate the charts.  Thanks to Patrick Husting for the original heads up on this.

And while on the subject of dashboards, can I also recommend this demonstration PerformancePoint site from Richard Lees, which has loads of examples using Analysis Services, data mining techniques and PerformancePoint scorecards.

Last week Microsoft made all sorts of announcements about Office 2010.  I’ve been taking a look at Excel 2010, and in particular the new data presentation options.  It looks like this release will have some worthwhile new Business Intelligence features.  As well as Gemini, which will I believe be available as a separate add-in, this will be the first release which will let you create Sparklines, those little word-sized graphics which I first blogged about in 2007.

Here’s how they look:

You can read more about how they work in Excel 2010 over on the Microsoft Excel Team blog here and even more here.

Finally, I’d like to mention the Guardian Newspaper’s Data Store.  This is a fairly unique exercise by a national newspaper to make datasets they have created, frequently to support news stories, publicly available.  Most of these can be accessed as Google spreadsheets, also downloaded.  As well as being interesting datasets in their own right, they are incredibly useful if you want to populate demonstration dashboards, load sample lists, or create training exercises.  You can keep up to date on what they are doing on their blog or on Twitter.

Technorati tags: excel 2010, sparklines, dashboard, performancepoint

Help With Automating iFilter and PDF Indexing Support in SharePoint

The most popular post on this blog is still the one about Configuring PDF iFilter for WSS 3.0.  Today I saw a post on Notes for .Net which may help to automate this multi-step process.  What’s more the post says it works with a variety of iFilters including FoxIt and Adobe.

The Notes for .Net team have posted their solution, which adds a new stsadm command, on CodePlex here.  I haven’t tested it myself yet but will do on my next VM build.

Technorati tags: iFilter, PDF, SharePoint 2007, WSS