Home » Sharepoint

Importing Data into Sharepoint Lists from External Sql Server


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:

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

Best wishes,

Slava G

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 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....




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,





my client requirement is to develop a feature at the site collection level when activated it should bring all the security data to external sql database. the tables contain the following info in each table SPsite(site guid, url,title,description...) spweb( subsite guid,url,title,creatoruserid(from user table),date created) in the same way we have tables of splist, splistitems,spfolders,spfiles,users... all the tables are related with primary and foreign keys. the database is a relational database. we are asked to use console applicatioins and Linq to Sql to bring the data from sharepoint sites to sql database. we are using visual studio 2010 and sharepoint 2010. this tables are used to for checking which users are having what kind of permissions.

Can someone please explain  how to develop a (object model) console application using linq to sql which will be able to get all the data from sharepoint site collection to sql database


I have found multiple posts on multiple blogs and forums involving importing excel spreadsheets into sql server.

Unfortunately, I do not know how to do this for excel spreadsheets stored on a sharepoint document library.  I am pretty sure it is metadata there, and there is probably some way of converting it on the fly, but I do not know how to get to this data, as I read a blog regarding Sharepoint "not being a file share" (http://weblogs.asp.net/bsimser/archive/2010/08/26/sharepoint-document-libraries-and-horrors-oh-my.aspx)

I would appreciate any help.


Ryan A. Sternberg


I am trying to do something fairly simple.. allow my user to import thier data from Excel into SQL Server, where it can be used by the application.

To do this, I'm using the fairly simple code that is common everywhere I look:

strFilePath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fdlgFileOpen.FileName + ";Extended Properties=Excel 8.0"
connExcel = New OleDbConnection(strFilePath)
Dim ExcelCommand As New OleDbCommand("INSERT INTO [ODBC;Driver={SQL Server};Server=servername _
    \sqlservername;Database=MyriadSQL;Trusted_Connection=Yes;].[tblCostCodes] (CC_L1, CC_L2, CC_L3, CC_L4, CC_L5, _     
    CC_Description) SELECT * FROM [CostCodes$];", connExcel)
Catch ex As Exception
End Try

Now, on my development computer this code works perfectly.  The data imports, and I can go on my merry way.

On the TEST computer, this code throws the error: "ODBC -- Connection to {SQL Server} servername\sqlservername  failed."  This seems very odd to me, because I'm using exactly the same SQL Server and database to interact with the application, and all the other screens in the application work perfectly.  The test computer CAN connect to this SQL Server, but it keeps throwing this error whenever I try import data from Excel.

I've tried everything I can think of - using the server's IP address instead of the server name, sending the SQL Server administrator data along with the request, taking out the Trusted_Connection=Yes... nothing works.  What is going on here?  How can this code work fine on my computer, and hiccup on another?

Thank you for your help,



I have read and tried to export and import but nothing seems to be work out...please help me!!


we have UserList contains columns FirstName, LastName.
We have to create UI to import data from Excelsheet in to UserList.
want to implement import functionality in Sharepoint Designer 2007
Please suggest if we have any inbuild functionality for the same or ways to implement it.



I created a custom list in Sharepoint 2010 publishing site. In the list, I edited the form using Infopath. In the Infopath 2010, I created a secondary data connection by selecting the database option. Selected SQL Server option. Gave the username and password option. I got the list of tables in that database, selected the table and created the data connection. In the form, i used the fields from the secondary connection to fill few of the fields, using the rules. I didnt check the preview. I published the form to the list. When i tried to open the form in the sharepoint list, by clicking Add Item, I am getting error. The error was related to correlation error.

I tried deleting the secondary connection, made a external list, and connected this external list as secondary data connection. Now when i opened the page in the site, everything is working.

What could be wrong. Is it not possible to use sql server as database in the custom list for secondary data connection?

Also, in the Infopath options, I was not able to see the security tab, when opened using the sharepoint list. Is this a permission issue. If yes, how to make the page in full trust, while customizing through sharepoint list.

Thank you

I am looking to pull data for a Sql data base outside of sharepoint's and populate a list in sharepoint with that external data. Is there a way to do this in Sharepoint D esigner or any other means??

I am planning to connect a Sharepoint Custom list with an external Data-source.

External DB will be in Access 2007 format.

More Information:
I need a solution that we can connect our sharepoint list (one-way) to online db in a way that new  information is directly updated in the sharepoint list, whilst other info changed in sharepoint list  is not synced back to original value.

Is there a way to solve this?



I have seen numerous articles for the above, but they all just gloss over the step I am having a problem with, assuming nobody will have an issue with it. 

I'm trying to create an External Content Type so I can use it to display external data (from SQL Server) in an External List.  When I get to the step to add the connection and the SQL Server connection dialog box appears, I enter the server and database names and click OK with the Connect with User's Identity radio button still selected and I get the error message: login failed for user ''. The user is not associated with a trusted SQL Server connection.  I've tried to read up on the different purposes of the radio buttons on offer on this dialog box but don't know which I should really be using and don't think I can create a target application ID anyway in Foundation (for the other two radio button options) as that has to be done in the secure store services.  Can anybody help with this?



The question below is regarding the SharePoint External list and External Data.

I have a simple scenario where I have a simple text file containing customer data, and I have created a BDCModel "Customer" using Visual Studio 2010.

I have added "ReadList", "ReadItem", and "Create" methods and these are working perfectly. 

I have question about "ReadList" Method, and I m wondering if there is any way to filter the results based on some parameters that could be passed to this method.







Dear All,

Last week I had a discussion with one of my colleagues about "Where the SharePoint external List data is stored?", Is it stored in a SharePoint list and the SharePoint is synchronizing the changes between it and the original source, or SharePoint is saving only the connection information to the source and works as a representation interface for the external data only?

If you look at the following architecture you can say that it is stored in the SharePoint



But, if you look to the following architecture for the client app integration you can say that it's not stored in the SharePoint, since the client app is synchronizing with the data source directly




Any comments will be appreciated.


Kind Regards,



Hi folks,

I am new to SQL Azure.

We would like to deploy our database with data (now in SQL Server 2005) to SQL Azure.

As I understand from the link below, I can use the tool SQL Azure Migration Wizard.


But it also mentioned about "run the SQL Server 2008 Upgrade Advisor". Does this mean I can only import the dabase if my SQL Server 2005 DB passed the 2008 Upgrade Advisor check?


I don't have an Azure account for test now. So I can only count on your answers for this question for now.

Thank you,



HI All,

How to Import data from sql server 2008 to sql server 2000 ? (i don't have any idea about SSIS, expect this any chance is there? i was try with import functionality some records are copy after it was faild i have nearly 80 Lacks records)

Please help me how to solve this issue this is very urgent form me...

Thanks, Prasad.



I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i amĀ using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.

Please let me know.



Has anyone successfully imported data from Excel 2010 into SharePoint 2010? I'm new to the world of Sharepoint so I could be doing something wrong but I feel like I've followed the procedure but the results I'm getting are very erratic. My Excel spreadsheet is very straightforward - there are only 200 rows and 15 columns. I've tried exporting the data directly from Excel and also importing from SharePoint. With both methods I don't get an error message but when I check my List I see that only a small subset of the data has actually been imported into SharePoint. Why would this happen? I've tried the procedure about 20 times - sometimes I only get one row imported, othertimes I can get about 25 rows to be imported.

As a further complication SharePoint seems quite fussy about the kind of data you are trying to import. If I perform the process from Excel I get some feedback about this. One of my columns is a list of user surnames. Each time I tried my import SharePoint (or is it Excel?) complained that the Suname column would be imported as "multi-line" and gave me a cell reference. The cell in question had the surname "Avraamides" (it's Swedish) in it. SharePoint didn't seem to like the fact that the surname had two "a"'s next to each other. If I renamed the surname to "Avramides", SharePoint was happy to import it as a single line of text. This wasn't the only example. For instance I had to change a department name called "Operations" to "Operation" to get it imported as a single line data item. Once I'd cleared these anomalies I hoped that all my data would be imported but I just couldn't get this to happen.

I decided to have one more attempt. Since the first row of my spreadsheet always seemed to be successfully imported I decided to create a new spreadsheet and just copy that row 200 times (but just changing the contents of the first column to include a sequential number). I then tried to import the new spreadsheet but again only the first 20 or so rows got added to my SharePoint list. I just don't get it. Is this a bug or am I doing something dumb? I always get a message that says my data has been successfully imported but when I check it is always only a small subset. What happened to the other records and why don't I get an error message about them? Thanks for your time.



I have this issue, a customer has an old application that consists of a SharePoint 2003 site and a 'forms library' with forms listing information about physical servers. The infopath (2003) form is pretty straight forward and has a few dropdowns from lists and so on.

Now, we need to move this to 2010 and preferably to a list only using oob functionality.

I would like to know if anyone has a clue as to how I can get the data from the hundreds of xml files into a list, could be to excel and then to SharePoint, or any means Necessary.

I would prefer it if it can be scripted or done using standard tools from Microsoft, no 3rd party custom xml importer...(if it's not really good and free :-) )




I'm having many problems trying to migrate WSS3 to SharePoint 2010, so now I'm trying to recreate the entire portal just copying the lists with their data using list templates.

I already saw that SharePoint 2010 doesn't accept WSS3 list templates, but I'd like to know if there's another way to copy lists and document libraries with metadata to a new SharePoint Foundation 2010 portal.


Best regards,


Juliano Nunes

I created a re-useable workflow in SharePoint Designer 2010, which is triggers the workflow when a list item is added and works fine. I now need to enhance it with some code and imported it into Visual Studio 2010 (Re-Useable Workflow project). I set the Target List property correctly but when I log items from the list to the history log they are all blank? Are there any other steps to take? I notice in debug it refers to $ListId - any idea where this come from?

I can able to import data in List through copy from excel and paste it in List using 'Edit in Datasheet' feature.

But Suppose I have 5 Fields out of which I am only showing 2 Fields in List, other 3 are not in view of list but we can see them when we add/Edit/Display item in list.

In above case when I am doing  'Edit in Datasheet', its showing me only 2 fields to import, but I want to Import data for all 5 fields. 

Please suggest solution or any alternative solution for above situation?


Hi, I have an issue. I have created an external list of External Content Type using SharePoint designer 2010 that also inserts new items into SQL server database. In my sql table there are two columns of “datetime” data type that take value in the following format: 2010-08-14 00:00:00.000 when an entry is made directly or data import is done from excel to SQL server table. but when I use SharePoint external list to insert data to SQL server, it makes a wrong entry for the date. eg. If i select 12-Aug-2010 from date time picker, it submits date as following in SQL table: 2010-08-11 18:30:00.000. It always saves date of one day before the date selected in date picker of infopath form. How can I save the exact date selected from external list infopath form to SQL server. Any help is much appreciated.

date selected in sharepoint external list new item form: 12- Aug- 2010
Saved in SQL table as: 2010-08-11 18:30:00.000

Many thanks in advance.



<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure