Should I use BDC for this?

Question

I have a SQL database with some user names I can query from different tables. I need to import this information to a SharePoint list and then use this list for some lookup fields in the future.

The database is constantly changing.

My question is: should I use BDC for this?

I searched online, I can use SQL integrated service for this too.

What is the best way to achieve this?

Thanks a lot
 

---

bb

Answer 1

The great thing about the BDC is it connects to and consumes live data from LOB applications.

If you goal is to import and not use the live data then I would chose another method.

Do not import directly to the SharePoint tables. Use the object model or web services to iterate and add new list items programmatically.

Cheers!

Paul

Answer 2

So what you mean is that using SQL Integrate service won't get the data updated while BDC will get the live data?

 What is the reason that I should program to add the data to the list instead importing directly?

Thanks a lot!

---

bb

Answer 3

The reason you do not want to use SSIS (Integration Services) is that writing to the SharePoint database directly leaves you in an unsupported state. When you need to add (import) items to SharePoint databases you should ALWAYS use the object model or web services.

You stated you need to IMPORT the data not view from another source. If your goal is to view (not import) these user accounts in SharePoint via web part then the BDC is a good solution.

If you need a onetime import try using Access 2007. Connect to the tables in SQL and connect to the list in SharePoint and run an append query. No code needed.

If you need to import over and over you could try using SSIS but instead of loading data to tables send it to the SharePoint lists.asmx web service or a custom service that uses the object model.

Answer 4

Thanks a lot!

What I need it is importing this live data to SharePoint (so should be importing over and over again) and use it as lookup in different document libraries. So it is not for viewing data.

---

bb

Answer 5

You could still use BDC when you only need to show this as a lookup value in a document library or sharepoint List, because you can add a Bussiness data column : see this article how to add a business data column. So in that case you would still not need SSIS.

 

http://msdn.microsoft.com/en-us/library/ms548808.aspx

 

---

Jan D'Hondt - Database and .NET development

Answer 6

I will give it a try... thanks!

---

bb

Answer 7

There is a catch - you would need to frequently refesh the BDC data as it is actually cached in  the list!