Monday, July 30, 2007

Sync Your Database to SharePoint Using SSIS

Just a heads up that DevX just published an article of mine today. The article is entitled Sync Your Database to SharePoint Using SSIS. The article covers how to import and export SharePoint list items using Collaborative Application Markup Language (CAML), SharePoint's web services API, and SQL Server Integration Services.

The latter half of the article is a fairly detailed how-to, but the former half covers what SharePoint lists are, what SSIS is, and why you would want to use them all together. I hope you find the article useful, and feel free to comment here if you have thoughts on the article.

14 comments:

Raging said...

hi sir your application is very helpful to us. may i ask if you have a VB version of this application for SSIS sync in Database? thank you very much and god bless

Derek said...

Hi,
I need to do something similar, but I don't want to delete everything in the list beforehand as some of it is content managed. I need to only add the new records in the DB to the list.
Any ideas?
Cheers

Lee Richardson said...

Raging,

I'm sorry I really only work in VB when necessary, but the concepts should still apply. If you convert my library code to VB please post of a comment of where to find it, I'm sure the community would find it very useful.

Lee Richardson said...

Derek,

These are fairly standard Data Warehouse syncing issues and there are a number of standard techniques to resolve. I'd suggest picking up a DW book for the general concepts.

As far as the details of retrieving from SharePoint, the article and sample code should be sufficient for retrieving data and comparing for example last modified dates. If not please provide a more specific question as I have done this type of thing and can probably provide guidance.

todd.parker@live.com.au said...

Your articale was great and is working well, however the createsynclist creates lists that does not have the "connect to Outlook' option like a contacts list does. I can see that there is a section in the listhelper file in the Sharepointservices project where you mention 100 = custom list, 101 = document library. 104 = contacts if i am not mistaken however i cannot figgure out where or how to specify this so when the exe is run it creates a list that uses the default contacts view and therefore has the "connect to outlook" option.

you can email me at todd.parker@live.com.au

Thanks again.

Anonymous said...

Lee, in your article, "Figure 1" clearly shows a Visual Basic project being created, but the code you provide in "Listing 1" is clearly for Visual C#. I hope I'm the only one that pulled my hair out on this one before figuring it out...

:-J
Jeremy

pasquale said...

I have VS 2005 on a client machine and not on the MOSS server. How can I register the SharePointServices.dll by using the gacutil command? Thanks

NarasimhaMurty said...

Hi,

Thank You very much for the post. I have downloaded your code, and tried to run the code with some changes, i am getting the error

Error: 0x1 at Script Task, System.IO.Stream GetRequestStream(): The remote name could not be resolved: 'nic-user-dev'

any suggestions?

NarasimhaMurty said...

Hello Sir,

I am able to compile the code successfully but after compilation in the output i am getting the result as zero rows have been written. There are 31 rows in my database. Any suggestions of why its not writing rows to the list.

Kevin Idzi said...

A better solution is now available - a source /destination adapter for sharepoint which handles all of that for you automatically. Enjoy.

http://www.codeplex.com/SQLSrvIntegrationSrv

Jon said...

I got into a bit of an SSIS nightmare for my synchronization scenario, then randomly stumbled on this product that does it:

http://www.bryantdesignnetwork.com/ProductDetails.aspx?productID=df4fce13-59b3-4045-a01e-eb0757a96a53

No code to write or anything so it was nice

Olya said...

Hi Lee,
Your solution is very helpful. I need your advice.We need to synchronize more than million records. Now we are looking how to improve performance.
Will be grateful for help.

Lee Richardson said...

Olya: over a million records is out of my league. Maybe the product Jon suggested?

Anonymous said...

Hi Lee,

this export from SSIS tech is working as expected. But only the document data ( in binary format) available in SQL Server is not exported to sharepoint column as proper datatype is missing.

Can you suggest what must be the Datatype of the Column in Sahrepoint to get the binary data export from sql server via SSIS.?

Thanks.
SSN