The WorkerThread Blog

we know stuff so you don't have to

Archive for August, 2009

Use Integration Services to Import SharePoint List Items to SQL Server

Posted by workerthread on August 28, 2009

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)

spResponse

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

SQLResponse

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:

Dataflow

And map columns like so –

Mappings

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 –

ResponseList

Into my SQL Server table –

ResponseData

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!

Posted in Business Intelligence, Microsoft SharePoint, SQL Server | 18 Comments »

SharePoint in Plain English

Posted by workerthread on August 15, 2009

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: ,

Posted in Microsoft SharePoint | Comments Off on SharePoint in Plain English

Checking Your SQL Server Jobs

Posted by workerthread on August 12, 2009

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.

Posted in SQL Server | Comments Off on Checking Your SQL Server Jobs