none
BizTalk adapter RRS feed

  • Question

  • Hi Everyone,
    Is it possible to syncronize (map changes between) a sharepoint list and an SQL table? either via an adaper(SQL, SharePoint, Flat File) or using sharepoint web services?

    Thanks in advance

    Bob

    Monday, April 6, 2009 3:29 PM

Answers

All replies

  • Hi Bob,

    I don't have much knowledge from sharepoint world. But as I know, sharepoint web parts can talk to web services to get the data or to call a particular function hosted by a service (and this happens without writing a single line of code!!).

    BizTalk adapter pack v2 (which is in Beta and you can download it from Microsoft connect site) contains SQL adapter, which you can use to expose SQL server functionality as a service (we call it "Hosting an adapter service". You can host this service in IIS or in self host environment).  And then make the sharepoint web part talk to this service to keep the data in sync.


    Another probable solution would be using the ".NET Framework Data Provider for SQL Server"  (http://msdn.microsoft.com/en-us/library/kb9s9ks0(VS.80).aspx). You can write a custom webpart in .net, which will use this data provider to talk to SQL server.


    Hope this helps,
    Thanks
    Gaurav
    Wednesday, April 8, 2009 7:33 AM
  • hi, many thanks for the reply.

    i did a bit of googling whilst waiting for a reply and discovered some people where using "SSIS" to import data into sharepoint lists via the lists.asmx sharepoint web service. having never used SSIS before does anyone know if it's possible to sync individual data changes or does it just re-import and overwrite everything in the list? essentially like dropping a table and then re-creating it with an updated source?

    thanks.
    Wednesday, April 8, 2009 2:35 PM
  • Wednesday, April 8, 2009 3:17 PM
  • Hi Bob,

    I can think of a Hacky way to do this.. using the Features of Excel 2007

    This is how you can syncronize sql data to excel table:
    http://office.microsoft.com/en-us/excel/HP102017101033.aspx?pid=CH100648471033
    look at these sections:
     - #Understanding
    data source support in Excel and Excel Services > Refreshing data
     - Importing data from Microsoft SQL Server

    This excel 2007 add-in Syncronizes the Lists with Excel table - http://msdn.microsoft.com/en-us/library/bb462636(office.11).aspx

    The trick is to Sync data from Sql Server to Excel and then Sync data from excel to SP Lists using the Excel add-in.
    Please note that this is a hacky way to do this and use this only if the Scenario is Simple and Straight forward.

    HTH
    Sreeram
    Wednesday, April 8, 2009 6:59 PM
  • hi, many thanks for the varing solutions. much appreciated.

    i'm now thinking about going down the line of coding my own sharepoint application that uses the sharepoint API to add and updates rows in a list:

    http://www.sharepointblogs.com/jimyang/archive/2006/12/05/update-wss-3-0-user-profile-programmatically.aspx

    (i realize that is for updating the user list, but surely similar code can be used for a standard list?)

    My only concerns in doing this is 1. the question above. and 2. that i can get server 2005 to notify the sharepoint application of a data change which it will then pull out based around some form of row identification?

    can anyone verify this type of solution would work? ( i realize i've now strayed into sharepoint )
    Thursday, April 9, 2009 8:39 AM