Synchronise SQL table with SharePoint List

Feb 11, 2009 at 3:53 AM
Hi

I am new to SSIS and currently using the Sharepoint List Adapters to attempt to synchronise a table in a sql database with a sharepoint list. Ideally I would like it to be able to sync both ways, but will be happy with the table being the source and the sharepoint list the destination (ie all modifications will be done outside of sharepoint)

I have successfully been able to populate a sharepoint list with the contents of the table using the destination adapter by 'igonoring' the ID field. With this approach everytime I run the package it will create a new row for each item, therefore creating duplicate entries. I have considered the option of first deleting all the rows in the Sharepoint list, but as I will be using the sharepoint list as a lookup to many other lists, this is not viable as the sharepoint id of the new items will be different.

I then configured the destination adapter to map the id from the table in the database to the Sharepoint id, but this then introduced its own problems. I was successfully albe to have it update items that had previously been added to the list with a matching id, but the id's of the items in the sql table will not always match the sharepoint id.

Also, if I was to use this method, When I added a new row to the table (and also updated a current row) and ran the package, an error occurred and did not create  the new item in the sharepoint list (the updated row was successfully updated in the sharepoitn list). I understand why this happended as was stated here: http://www.codeplex.com/SQLSrvIntegrationSrv/WorkItem/View.aspx?WorkItemId=8581 due to the fact the provided ID must exist, but I am unsure how to get around this?

Basically I want a Sharepoint list to be a duplicate of the database table, with the Sharepoint list able to the be used as a lookup in others lists in the Sharepoint site. This requires that the sharepoint item id be maintaned.

Hopefully someone can point me in the right direction on how to achieve this.

Thanks

David
Coordinator
Feb 11, 2009 at 7:07 AM
To approach this, think of it in terms of SQL. If you had a table with a identity ID column you could not get rid of, but wanted to do the same thing - how would you do it?

An approach I can think of is to keep the sql id in a new column on the sharepoint (original id), and then when you are updating the list, first load the list, and join the list from SP with the list from SQL.  When they match, do the necesary updates, when the one in SP doesn't exist, insert, and if the one in the SP is not in the SQL, then delete it.  This way the sharepoint id will remain the same with the sql id associated, and you'll be able to do what you need elsewhere. 
Mar 3, 2009 at 3:03 PM
This is exactly what i'm looking for..
I appreciate if you can provide a more detailed steps on how to achieve this Sync, with SQL table as my Source n SP List as my destination.
I played a bit with List Destination and successfully uploaded the content based on few SQL queries...but I didnt achieve the Sync totally..

David/Kevin,, It would be reallly great if you can provide me some steps on this..<I'm a SharePoint developer and dont have expert grip on sql db/ssis>
 
Thanks a lot Kevin n Team  for providing these Adapters and making things easy.. 
Mar 4, 2009 at 9:56 PM
Hi MasheshSPS,

I haven't had much more of a chance to play around with getting the sync to work, but it is still an issue that I will need to get working at some stage in the near future. If and when I do find a way, I will post back and let you know how I got it done.

I am always keen to hear of any suggestions to try out.

Thanks

David
Mar 4, 2009 at 11:13 PM
All, I'm doing a similar thing and will post my solution when I finish. Using the hint from Kevin, my approach is generally to add Control Flow around the Data Flow.  Being that I don't have any experience in the SQL Integration Services UI doesn't help any, but it looks pretty intuitive so far.


pritish.
Mar 5, 2009 at 2:40 AM
Thank you David,

I started looking for another approach on this, luckily i found some guidence from one of the sample projects on CodeProject,
sample name - WSSdatabaseSync , http://www.codeproject.com/KB/sharepoint/WSSDatabaseSync.aspx

I'm working on using the list Adapters for one initial bulk load and following SYNC operation on the above project guidelines.

All the best,,

Regards,
MaSh

On Wed, Mar 4, 2009 at 5:56 PM, david_17 <notifications@codeplex.com> wrote:

From: david_17

Hi MasheshSPS,

I haven't had much more of a chance to play around with getting the sync to work, but it is still an issue that I will need to get working at some stage in the near future. If and when I do find a way, I will post back and let you know how I got it done.

I am always keen to hear of any suggestions to try out.

Thanks

David

Read the full discussion online.

To add a post to this discussion, reply to this email (SQLSrvIntegrationSrv@discussions.codeplex.com)

To start a new discussion for this project, email SQLSrvIntegrationSrv@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com


Mar 5, 2009 at 5:43 AM
mash -

the big caveat about the BDC is the licensing -- in SP 2007, I'm pretty positive you need to buy the SharePoint Enterprise license in order to use BDC and import application definitions, etc. 

pritish.
Mar 5, 2009 at 12:29 PM
Yes Pritish, You are Right.
 But here in the later half of the article it discusses a workaround on achieveing this without BDC.
I worked on a basic console application sample it worked for me
 
-MaSh
Aug 11, 2009 at 11:18 PM

Hi!

I just want to share how i did the synchronization of SharePoint List through SSIS without any programming code and it works!

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:

1 Output Name: Create    Condition: ISNULL( ID )

2 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 Udate, 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"

That's it! Good luck! Email me if you have questions for this post.

-allen

Aug 11, 2009 at 11:54 PM

Thanks allen, this sounds very promising. I am keen to try it out when I get a chance.

Just to confirm, will your method keep both the SharePoint list and DB table totally in sync, ie:

  • allow the SharePoint list to be used in lookups (maintain item id's)
  • is data able to be created, updated, deleted both ways (from SP list and also DB)

Im sure I will have more questions at a later date but once again...thanks!

Aug 12, 2009 at 3:35 PM

You're welcome David! For your concerns.

  • allow the SharePoint list to be used in lookups (maintain item id's)

Yes it will allow your list to be used in lookups as the IDs are not changed. That is, as long as you dont change the key related to that ID in Sharepoint.

  • is data able to be created, updated, deleted both ways (from SP list and also DB)

No it does not work both ways. It will synchronize the SharePoint List with the SQL table. It would be hard to do it at the same time as you have to identify which one should be the correct data. What if someone updates the same record at the same time on both sources? Which one should you follow?

But if you really have too, you have to create another package that will synchronize your SQL table with the SharePoint List. You may follow the same concept given, just the other way around.

Coordinator
Sep 29, 2009 at 5:06 AM

Allen - thank you for that detail, that is exactly how I do this as well :) Thanks for so nicely writing that out.  I need to make a little best practice wiki or something on this codeplex for this information.

Coordinator
Sep 29, 2009 at 5:08 AM

Actually, for the second issue, you can do a two way sync.

However, I am not able to write the full detail as you did, however, if you load both sources in, you could do a full join and then for any rows not in SP which are in SQL, add them to SP.  Any rows in SQL not in SP, add them to SP (you'd never 'delete' any rows however), and if the rows do exist - you can check against the update dates and modify the source with the greater date.

So it's really just in how you work the data flow.  The data flow is pretty much step by step, and the SP adapter will let you do what you need - but you need to be able to express it.  There are some other Codeplex tools, in addition to this one, which sync tables and the such as well if that is all you want to do.

 

Mar 10, 2010 at 2:49 AM

Allen, thanks for the details. I have been searching for updation on sharepoint lists and found the way out here..Iam new to integration and this question may sound silly..

 I used create and update sharepoint lists destinations...Even if only one row is updated, its looks like all the rows in the destination are getting updated.

Would this affect the performance on the system..Currently we have 1800 rows in our sharepoint list and when i update one row in my sql-database, I could see that its updating all those 1800 rows..

Is it possible to update just that one row which changed..

 

thanks,

Apr 26, 2010 at 9:13 PM

Brillant, exactly what I needed!

Thanks

/Robin

Coordinator
Jun 2, 2010 at 9:11 PM

Anits - Absolutely you can update just one row -- that is definitely preferred.

However, If you update one row but keep all of the rows in the dataflow - it will send all of the rows to the destination, same as with any data flow.

If you make an update, you should fork the data flows maybe using a conditional or c# script to make a filtered output path for the 'modified' rows.  When that gets into the destination component, it will pump it out to SharePoint. 

Generally, any destination component have no state and do not know which rows were modified, that is the job of your data flow to filter that information.