Sharing SharePoint Knowledge

Technical Solutions for SharePoint Server 2010

No Code Solution: Connecting to external systems using BCS and SharePoint Designer

Posted by Chandrasekaran C N on June 17, 2010

Hi All,   

Microsoft SharePoint is a powerful tool to interact with external system such as SQL Server, SAP, Sibel, Oracle, etc., from MOSS 2007. But there are certain limitations in MOSS 2007 like  

  • Read-only access to external systems
  • No intiutive ways to create application definition file (XML)
  • Possible error-prone due to manual creation of XML file

With the latest version of SharePoint 2010, the limitations aforesaid has been addressed and new functionalities are also included.   Microsoft SharePoint Designer 2010, the successor of SharePoint Designer 2007, is used to create no code solutions that interact with external systems with all sets of CRUD operations.  

Interactions between the SharePoint 2010 and external systems is made possible using the new feature called “External Content Types” (ECTs).  An external content type is a special content type contains all the metadata that helps to access the external systems.  External content type is something similar to “Entities” in business data catalog (BDC).  

As we know, in SharePoint 2010 there a lot of services introduced, one of them is Business Connectivity Services (BCS) which uses the external content type to interact with external systems.  

To view all the external content types available in SharePoint 2010, go to Central Admin -> Manage Service Application -> Business Connectivity Services  

Fig1: Existing External Content Types

Okay. Now we will start creating a no code solution to interact with the external systems in SharePoint Designer 2010. We will split this walkthrough into sections so that it will easy to follow Section 1: Creation of External Content Type 

  1. Open up the SharePoint Designer (SPD) 2010 and select the site you want to create external content type
  2. In the left hand navigation panel, select External Content Type option. This will show up all the existing external content types, if available
  3. On the ribbon menu, select New External Content Type as shown in the diagram below

Fig 2: External Content Type creation in SPD 2010

Once we created the external content type, it will ask to fill some basic information such as Name, display name, etc., about the content type as shown below  

Fig 3: Basic Information about External Content Type

One important option we need to fill is Office Item Type. There are five different options available namely  

  • Generic List   
  • Appointment
  • Contact
  • Task
  • Post

In this case, we select “Generic List” office item type that maps to a SharePoint custom list.  

Section 2: Connect to Datasources  

Once section 1 is finished, we need to configure the connection to the datasources. In Fig 3, click on the link (arrow marked) to configure the data connection. This will open up the screen below to configure the data connection  

Fig 4: Configure Data Source Connection

In the above diagram, click on the Add Connection button, this will popup a window asking which type of connection we want to connect to. Those are  

  • .NET Type – Use to connect to any custom .NET component
  • SQL Server – Use to connect  to existing SQL Server database
  • WCF Service – Use to connect to WCF Service

Fig 5: Data Connection Types

Select SQL Server type and click ok to continue.  

Provide the SQL Server information and select ok to continue in the SQL Server Connection window as shown below.  

Here in this window there are three different options on how to connect to SQL Server  

  • Connect with User’s Identity – Indicates to connect to SQL Server with current user identity
  • Connect with Impersonated Windows Identity – Indicated to connect to SQL Server with impersonation of other’s windows identity
  • Connect with Impersonated Custom Identity – Indicates to connect to SQL Server with impersonation of other’s custom identity (non-windows identity such as SQL Server identity or some other)

Last two options are mainly depends on the Secure Store Service configuration, which is configured in the SharePoint central administration and it is similar to Single Sign-On in MOSS 2007.  

Configuration of Secure Store Service is out of scope in this post. May be I will post later sometime on how to configure the same.  

Section 3: CRUD Operations  

After finish providing the SQL Server information, it will connect to database and shows up the provided database instance as shown in the above diagram (Fig 4). In this case it is “AdventureWorks”.  

Expand the AdventureWorks to select the database objects such as Tables, Views, etc.,  Select any table you want to show up in the external list and right click the table name to define the operations we want to perform like create,update, etc.,  

Fig 6: CRUD Operations

In the above diagram, there are several operations available to define. In this walkthrough, we select “Create All Operations” meaning we need to perform all CRUD operations on this table.  

Once we select the above operations, SPD opens a wizard like dialog to configure additional parameters on the data source such as limiting the data to pull from external data sources at a time, to provide filter conditions, etc.,  

Below screenshots show how we provide the necessary information in order to complete the wizard.  

Fig 7: Startup Wizard


Fig 8 : Parameter Settings


Above diagram shows the parameter configuration, we can set which column should be read only, required, etc., One of the  property we need to focus is “Office Property”. This property needs to set for each column, if we select the office item type in Fig 3 apart from “Generic List”.  

Fig 9: Filter Settings

Following steps describes on how to provide filter conditions

Click on “Add Filter Parameter” button

Select the data source element for which we need to provide filter condition in the above diagram

Click on Filter option (Click to Add) to open the dialog box as shown in the below diagram

Fig 10: Filter Condition

  • In the above diagram, select filter type as “Limit” and Filter Field as “WorkOrderID” and click ok.
  • Provide any default value for the limit and in this case it is 100 as shown in the above diagram.
  • Finish the wizard by clicking the finish button and save the content type in the SPD.
  • After saving the external content type, you can view the created ECT in the “Business Connectivity Services” section in the central administration

    Fig 11: Created ECT in Central Administration

    Now we can able to create the external list in SharePoint site. Below screenshot shows how to create external list in SharePoint site. It is very similar to normal custom list creation except we need to select which external content we need to use for this list.

    Fig 12: Create External List


    Section 4: Assigning Permissions 

    In order to view the data in the SharePoint external list, we need to provide necessary permissions to the external content type. Below steps show on how to assign permissions to the external content type

    • Go to Central Admin -> Application Management -> Manage Service Applications
    • Select Business Connectivity Service option
    • Select the external content type to which we need to provide permissions
    • Select “Set Object Permissions” option as shown in the below diagram

    Fig 13: ECT Permissions

     Select the user and assign permissions to the selected user as shown in the below diagram

    Fig 14: Assign Permissions to ECT

    Click Ok to set the permissions. Now we are able to view the external data in the  SharePoint list with no code.

    Fig 15: External List with Data

    Now, it is very easy to interact with external system using BCS and SharePoint Designer with no code.

    Chandrasekaran C N



    Leave a Reply

    Fill in your details below or click an icon to log in: Logo

    You are commenting using your account. Log Out /  Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )


    Connecting to %s

    %d bloggers like this: