Integrating SharePoint Data with SQL Server

Feb 23, 2010 at 10:24 PM
Edited Feb 24, 2010 at 3:09 PM

Hi, I'm looking for a solution that i can use to import list data into SQL tables for BI reporting purposes.  I was going to write a few SSIS packages (or solutions) in order to extract the necessary data using the web services provided by SharePoint.  However, i ran across a comment in the MS SQL Server Magazine article outlining a similiar process, which directed me to SLAM.  I've installed SLAM and my dev server recognizes all of the components.

My question is, can i use SLAM to only extract data from lists in SharePoint?  Better yet, can i extract existing data from these lists and then sync up any additions OR modifications to any of these lists?? 

BUT, if the items in these lists are deleted, DO NOT remove those related items from any of the SLAM tables that represents the linked up SharePoint list data??  Hense the BI part of the assignment...

I suppose if all SLAM did was sync up the data, i could then write an SSIS package to copy data out of those SLAM tables and move it over to a designated BI database.  I was just curious on the extent of what SLAM can do when referring to the subject of using SLAM just to sync up data, not necessary handle the Parent Child issue that SharePoint 2007 currently has.  Thanks!

Article:  http://www.sqlmag.com/Articles/ArticleID/101656/pg/5/5.html

Developer
Feb 23, 2010 at 10:43 PM

Hi there.  Yes, plenty of folks use SLAM for BI reporting purposes.  By default SLAM will synchronize the data in your slammed lists with SQL and keep it up to date in real time - including deletes.  For your requirement to NOT delete items, you could create a SLAM profile that simply didn't perform the delete function.  See the hierarchy example for an example of creating a SLAM profile.  Another option would be to create a second set of tables and use triggers to do inserts and updates as data is added into the slammed tables but not do anything on delete.

Yours,

Allan

Feb 24, 2010 at 3:21 PM

Thanks for the quick reply, i like the SLAM profile idea a lot and will look into it. 

I have just one other question that concerns continued syncs after the initial setup and sync of a list and it's corresponding SLAM table.  If the SLAM profile is setup the way you described correctly on my part, and say in the future we change the design of the list, say add new fields, remove old fields (at the very least, add new fields), after the SLAM config file for that list is modified and the new fields are added to (or removed from) the config file, will it continue to sync up without any issues?  Or are there any limitations to the types of modifications one can do to lists that have been already setup with SLAM.  I know I could always disconnect the link and pull my data out into another table to preserve what i have, but wanted to know the extent of what can be done to the SLAM environment.  Would new fields sync, old fields remain static or filled with a NULL entry?

I would really like to use SLAM to ease this data requirement for my BI information.  I would feel better knowing that any data in syncs would remain under the special SLAM profile if the parent list changes in any way.  if not, please let me know, or if you have a SLAM product limitations document that you could direct me too that would be great as well, appreciate your time, great product.  Haven't checked yet, hope it'll be compatible with 2010, at least the table syncing part! =)

-Patrick