Try your hand at the Business Connectivity Service – part 1

One of my clients wants to use data from an external data source as one of the metadata fields within the document management system. I was wondering how this might work, having never seen the BCS in real action before. So, I used my demo environment to set-up a BSC connection. All in all, it took just over five minutes.

Sure, it was a very simple connection to a SQL database, without any complex security issues….But, if you need to get an quick overview of the BCS, give it a try.

You need:

• SharePoint 2010
• SharePoint Designer 2010
• SQL Server 2008 R2
• A demo SQL database (I used the Microsoft Adventure Works free demo database).

Let’s do it:

Make sure you have the Business Connectivity Service up and running. You can check, start or create this service using Central Administration | Application Management | Manage service applications.

Next, create a site for testing the BCS connection. You can also use an existing site, as I did.

Open the site using SharePoint Designer.

Now you can add BCS connections to the site. These connections can be used throughout the site and are managed by the BCS service. Go to External Content Types and click External Content Type. Now, you can create the external connection.

Provide a name for this connection (the display name will be the same) and click on External System. At this stage you still have not connected to an external source. Using External System, we can set this up. Click on “Add connection” and select the SQL Server type. You will be prompted to provide the servername and the credentials. For this demo, we will only provide the server name and database. The sample database name is “AdventureWorksLT2008R2”.

If all goes well, then the database will be opened. You will see different tables. For this demo, we will use the “Product” table. Right click on “Product” and select “Create All Operations”.

After this, click Next 2x and Finish. Your connection has now been created, but has not been published to SharePoint. Simply save the connection (CTRL-S).

One more thing….

You will need to grant the user the rights to use this connection. To do this, go to Central Administration, Application Management, Manage service connections, Business Data Connectivity Service and go to the context menu of the connection. Select Set Permissions.

For this demo, grant a demo user all permissions and press OK. The connection can now be used.