Friday, January 22, 2010

Synchronise SQL table with SharePoint List

I wanted to share how I was able to get synchronization of a SharePoint List through
SSIS. Below I have provided an outline of the steps I took.

  • First, I had an OLE DB Source, then a Data Conversion to convert those strings and
    then Sort it on its key.
  • Second, I had the SharePoint List Source that has to be synchronized, Data Conversion
    to match the key type and then Sort it on the key.
  • Third, Merge Join this 2 sorted data using Full Outer Join.
  • Fourth, add a Conditional Split with the ff output:
    • Output Name: Create Condition: ISNULL( ID )
    • Output Name: Delete Condition: ISNULL([Your Key])
    • Default output name: Update
  • Lastly, Add 3 SharePoint List Destination for Create Delete and Update with the
    ff note:
    • For Create, do not map the column "ID" and Batch Type should be "Modification"
    • For Update, map the column ID to the ID you got from merging, Batch Type should
      be "Modification"
    • For Delete, you only need to map the ID, and Batch Type should be "Deletion"

    How the layout looks, sometimes a pictures can help:


3 comments:

fsugeiger said...

Over the past week I have continued development on the List Adapters. The addition of the expressions on the DFT level for Source and Destination is very helpful. Next the addition of the CAML query in the expression allows for more of a focused list of records instead of returning the entire list. Working with a list of 80,000 records the CAML query comes in handy.

http://workerthread.wordpress.com/2009/08/28/use-integration-services-to-import-sharepoint-list-items-to-sql-server/

http://svengillis.blogspot.com/2008/11/tip-yet-another-caml-query-tool.html

HelloIT said...

Hey,

thanks for this helpful post - it saved me a lot of time. Quite nice to see, that this is simple but effective.
After testing this with a large dataset i have maybe an upgrade for you.

I modified the conditional split:
- Create (like your post)
- Delete (like your post)
- Update (Compare each metadata - was there a change?)
- Default: "None"

Best regards,
Kai

Paul Gallagher said...

Alternatively AxioWorks SQL List will set up replication and keep the SharePoint data synchroinsed in normalised SQL Server tables. All changes to Sharepoint list schema is automatically be reflected in SQL. It can be installed anywhere as a windows service

http://www.axioworks.com

Post a Comment