Home » SharepointRSS

Importing Data into Sharepoint Lists from External Sql Server

Hi,

I am looking to import data from  a Relational SQl Server Database that is hosted externally by a 3rd Part vendor. My task is to import all the data that is entered by the external customers into the Sharepoint lists. The external users are presented with a free form Registration page where they enter their data. For the internal users, we want to pull that data in real time and display in lists.

So inother words as soon as the data is entered on the form and user submits it, the database and the sharepoint list should reflect changes simultaneously.

Please give direction as to how can this possible.
 

12 Answers Found

 

Answer 1

Hi srituraj,

Here's the discussion we had about similar task:
http://social.msdn.microsoft.com/Forums/en-US/sharepointdevelopment/thread/02e82c01-5794-49a1-a4a3-873eb8aad9b5

Please have a look, maybe that will help you to choose the approach.

Best wishes,

Slava G
http://wyldesolutions.com
http://wyldesharepoint.blogspot.com
 

Answer 2

Thanks Slava For pointing me in that direction. However would have any suggestions as to what approach would be handy if we want to update the data  in real  time.

Ideally I want to trigger an event that can replicate the changes on the the content databases ( hosted  locally for sharepoint  ) as soon as they are done via free  form entries on the Back end sql  database ( hosted ata 3rd  part location)


Please suggest.
 

Answer 3

The best tool I know of as long as you have SQL and SSIS running SharePoint SSIS list  Adapter.. There is an MSDN article... http://msdn.microsoft.com/en-us/library/dd365137.aspx that describes how to use the tool, but its really much easier than the article may suggest...

Integration Services package developers often ask about the best way to get data  out of or into a SharePoint list. The SharePoint List Source and Destination Sample available on the Microsoft SQL server  Community Samples: Integration Services page  on Codeplex provides an optimized solution with an easy-to-use interface. The sample also includes an API for accomplishing these tasks efficiently outside of Integration Services.


There a few different ways to extract or load SharePoint data:

Use the SharePoint APIs to add or remove items one by one. Use the SharePoint Batch API to generate XML and submit the XML. Use the lists  Web service, which uses the same XML as the Batch API.

Calling the Web service is a powerful way to transfer data to or from SharePoint, whether or not you have extensive rights in your SharePoint environment, because it does not alter the SharePoint server itself in any way. The Web service also uses the Batch XML structure, which provides better performance than the server APIs for extracting or loading data.


Features of the SharePoint List Source and Destination


The SharePoint List source and destination use public SharePoint Web services and have several features that enhance their performance and their ease of use:

Only the fields that you want are returned from SharePoint. Large lists are not transferred all at once. They are paged in batches, with a configurable batch size. Column type information from SharePoint is used for mapping to Integration Services data types CAML queries can be added to the query to filter the rows to be returned. Update and Delete operations from an Integration Services package are simple. Important custom properties of the source and destination can be set by using Integration Services expressions.


-Ivan

Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.
 

Answer 4

Thanks Ivan, I have no experience with SSIS and also wonder if that would be a real  time solution. One approach that I am looking into is using BDC (definately with 3rd  party toos). Do you have any suggestions for me while I head this approach.

Also is it recommended to do real time  sync or may be do a daily batch process where data  is syced into the sharepoint.

Please suggest.

 

Answer 5

Depends on the size of your ETLs... Most Large do a combination of both batch processing and real  time, it depends on the type of data  and the effect it can have on the busines SSIS  is used to Puss and Pull information from various sources, SSAS helps us Model Metadata relationships, while SSRS delivers the results through Custom Reports, or Ad Hoc Queries.... SharePoint is the Presentation Layer where users  interact with the Models...

Traditionally BDCs have been used to gain access to Information in other Enterprise Content Management systems like Documentum and Filenet. However, integrating Great Plains, CRM, PeopleSoft, and Oracle Financials I believe has led Microsoft to develop the SSIS Adaptors for SharePoint that gives us a much more efficient method of working with large ampounts of data and Allows our DBAs to be more involved with the delivery of the business solutions...

Yeah there are some free  tools on Codeplex BidsHelper as well as additional tools from Microsoft and a more documentation. My advice if to use the tools and attend Microsoft and Third party briefings on sql  2008 and SharePoint 2010....


Cheers,


-Ivan

 

Answer 6

I think Ivan has provided a detailed response regarding real-time synchronization on the SQL Server side.

In terms of SharePoint there could be couple of ways:

1. List Event receiver
2. Workflow
3. Timer service

If you have any questions regarding that - please let me know.

Best wishes,
 

Answer 7

Hi Salva,

After discussiing with our internal users  we have decided that we can sync data  once a day from the external  Sql Server.

I have reserached around to find out a tool called Moss BDC Design Studio that generates XML from Datatables. I am planning to use this tool against a dataview residing on the external server. pull  of the view , convert to XML and then populate the List.

Would you mind outlining me an approach as to how can I automate this process.

These are step :
1. Run Service every night to pull data from the dataview residing on the external sql  server : We need to connect securely.
2. Parse it to XML.
3. Move it into Sharepoint List.

Please sugget.
 

Answer 8

Hi srituraj,

Yes, the steps are right. What do you want me to suggest? How to develop the solution? :)

I haven't used MOSS BDC Design Studio myself, so can't say anything about it.

Best wishes,
 

Answer 9

Thanks for the feedback Slava.
 
 

Answer 11

Nice article Slava.Thanks for this.
 

Answer 12

Hi Srituraj,

I have exactly the same requirement. Users fills web application forms, which is connected to sql  server database. I need to synchronize sharepoint  list with this data  whenever user  inserts, updates web application form  data.

What is your final approach for this project. Can you give  me some suggestions?

Thank you,

Praveen.

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter