BCS with SharePoint Online

Introduction

Business Connectivity Services (BCS) is a feature of SharePoint which allows to connect to external data sources and perform CRUD (Create Read Update Delete) operations. The data sources can be one of the following:

• SQL Server
• WCF Service
• .NET Type

In this blog I will walk you through the detailed steps involved in implementation of BCS in SharePoint Online with WCF service as a data source. Below are the major steps required for implementation:

• Implementation of WCF REST Web Service
• Web Service integration with SharePoint Online(SP-O) through BCS

Implementation of WCF REST Web Service

Windows Communication Foundation (WCF) is a framework for building service-oriented applications. Using WCF, you can send data as asynchronous messages from one service endpoint to another. A service endpoint can be part of a continuously available service hosted by IIS, or it can be a service hosted in an application.

I will not explain a WCF service implementation here, since there are lots of blogs out there on WCF REST services implementation. Create new WCF service project from Visual Studio and add below CRUD operations:

• Create Item
• Read Item
• Read All Items
• Update Item
• Delete item

Let’s say CUSTOMER is a table in Database with below columns which are pretty much self-explanatory:

• customerID
• FirstName
• LastName

Operations supported by WCF service would more likely be as below:

        public List<Customer> ReadList()

        {

//Read all items Code goes here         

        }

        public bool Update(int customerID, string FirstName,string LastName)

        {

// Update item Code goes here           

        }

        public bool Create(int customerID, string FirstName,string LastName)

        {

// New Item Code goes here       

        }

        public void Delete(int customerID)

        {

//Delete item Code goes here            

        }

        public Customer ReadItem(int customerID)

        {

//Get Item  by ID Code goes here        

        }

You can follow this blog for WCF REST service implementation. Post implementation of WCF serice publish it to IIS and make a DNS entry of it so that we can use it over internet and can access it from SharePoint online site.

So assuming that our web service is ready we will move forward to the next step.

WCF Service Integration with SP-O through BCS

The External Content Type (ECT) is a core concept of BCS through which we can integrate external data with SharePoint. Open your site in SharePoint designer and follow some easy steps to create ECT.

    1.Open SharePoint designer, select external content types and click Add connections. Select WCF service as a Data Source.

    2.Fill the details in WCF connection dialog box as follows:

2.1 For the Service Metadata URL setting, type the URL of the service endpoint created in STEP1, appending the ?wsdl string to the end of the URL. e.g.https://www.abcd.com:41003/WCFCustomer.svc?wsdl.

2.2 In the Metadata Connection Mode field, select WSDL.

2.3 In the Service Endpoint URL field, type the service endpoint URL. For example, type http://www.abcd.com:41003/WCFCustomer.svc

2.4 In the Name field, type WCFCustomer.

Note: Our web service does not have authentication, so we can connect with user’s identity. Otherwise we need to create a Secure Store Application ID entry from SharePoint Admin center. Secure Store Application ID entry will have credentials which can be used to authenticate user to access a web service.

    3.SharePoint designer will validate the connection and it will get displayed in Data Source Explorer. Expand the connection and we will be able to see all the methods that we have implemented in a web service.

Note: User must have Edit and Execute permissions in Metadata store, otherwise it will give an error saying Access denied by BDC. We assign metadata store permissions to user through SharePoint admin center in BCS by select ting “Manage BDC Models and External Content Types”

    4. We need to define supported operations for the external content type.

1. Right-click the ReadItem method in the Data Source Explorer, and then click New Read Item Operation on the context menu.
2. In the Read Item wizard that opens, click Next on the Operation Properties page to keep the default Operation Name and Operation Display Name values for the operation.
3. On the Input Parameters Configuration page, notice that there is an error in the Error Messages group box about an identifier field for this external content type that should be specified. To do this, select the CustomerId field in the Data Source Elements group box, and then select the Map to Identifier check box in the Properties group box. This sets the CustomerID field as an identifier for this external content type, not only for this parameter. The error message disappears. Following figure shows the Read Item operation input parameters.

  Figure: Read Item operation input parameters

4. Click Next to continue to the Return Parameter Configuration page.
5. On the Return Parameter Configuration page expand the ReadItem node in the Data Source Elements group box.
6. Click the CustomerID parameter in the Data Source Elements group box.
7. Select the Map to Identifier check box in the Properties group box. Notice that field is automatically marked Read-Only.
8. Map the FirstName field and LastName field to Office properties. Select the FirstName field in the Data Source Elements group box, and then select the First Name (FirstName) property from the drop-down list for the Office Property setting in the Properties group box. Repeat this process for the LastName field, mapping it to the Last Name (LastName) Office property. Following figure shows the Read Item operation return parameter.
9. Click Finish to complete the creation of the Read Item operation.

Figure: Read Item operation return parameter

Similarly create External Content Type Operations for ReadAll, Create, Update and Delete operations.

    5. ECT is ready for use post implementation of all ECT operations. Click on ‘Create List and Forms’ in SharePoint designer and fill the details to create specific list and forms. It will create a list as well as forms which will be an InfoPath form.

Create an external list from SharePoint site using this external content type and that’s it. 

Hope it helps somebody to quick start with BCS and SharePoint Online. Any comments, questions are welcome. :)

SSRS Integration with SP2013

Introduction

SSRS (SQL Server Reporting Services) is a tool that help us to create, deploy and manage reports. It supports variety of data sources. SSRS can be installed in two modes viz. Native mode & Integrated mode and can be used in either of two modes. In this articles, I will walk you through SSRS installation in integrated mode and its integration with SharePoint 2013.

Software Requirements

    ·         SQL Server 2012 R2/2014 Enterprise Edition

    ·         SharePoint 2013

    ·         MS SQL Server 2012/2014 Add-in for SharePoint 2013

It will be a standalone server architecture, where all required softwares will be installed on single server. However we can extend it to multiple server. Just to keep the things easy, I will explain the Single Server Architecture.

Implementation Steps

Step 1: Install SQL Server Reporting Services and SQL Server Database Engine:

Run the SQL Server Installation Wizard to install SQL server.  While doing the installation, it will ask for feature selection where you can specify the features which are required to be installed.

In share features select Reporting Services – SharePoint and Reporting Services add-in for SharePoint as shown below:

On the Reporting Services Configuration page you should see the Install only option is selected. This option installs the report server files, and does not configure the SharePoint environment for Reporting Services.

Step 2: Install MS SQL 2014 Reporting Services Add in for SharePoint 2013

If the MS SQL 2014 Reporting Services Add in for SharePoint 2013 is not installed during SQL Server 2014 installation, please download and install it from this link.

The SQL Server 2014 Reporting Services Add-in for Microsoft SharePoint technologies allows you to integrate Reporting Services features with the collaborative SharePoint experience.

Step 3: Install and Configure SharePoint 2013

Run the setup of SharePoint to start installation. While configuring a SharePoint farm, specify the System Account (domain account) as a farm admin. Configure the SharePoint server to use above installed instance of SQL server 2014.

Step 4: Activate Reporting Services feature

       a.  Open Central Administration.

       b. Go to Site Settings

       c. Under Site Collection administrator, click Site collection Features

       d. Activate Report Server Central Administration feature

       e. Activate Report Server Integration feature.

Step 5: Register and Start Reporting Services SharePoint Service

The following are the common reasons why you need to manually register the Reporting Services service.

You installed Reporting Services SharePoint mode before SharePoint was installed. The account used to install Reporting Services SharePoint mode, was not a member of the SharePoint farm administrators group.  The necessary files were installed as part of the SQL Server installation wizard, but the services need to be registered into the SharePoint farm. The SQL Server 2014 release introduces PowerShell support for Reporting Services in SharePoint mode.

Open the SharePoint PowerShell and run following commands:

Install-SPRSService

Install-SPRSServiceProxy

Step 6: Create SSRS Service Application

Below are the steps to create a service application and a description of the properties:

1.       In SharePoint Central Administration, in the Application Management group, click Manage Service Applications.

2.       In the SharePoint ribbon, click the new button.

3.       In the New menu, click SQL Server Reporting Services Service Application.

Fill in the necessary details and the Service Application is ready to be used.

Step 7: Create BI site and Configure Document library for Reports

Create a new BI site from Central Admin by selecting Business Intelligence template in Enterprise section. Create a document library and add below three content types:

      ·         Report Builder

      ·         Report Model

      ·         Report Data Source

Step 8: Upload Reports and attach Data Source

You can create SSRS reports using SSDT or Visual Studio or SSRS Report Builder tool. Upload the .rdl (Report Definition Language) files and .rsds(Report Server data Source) files in document library. Attach the .rsds file to .rdl file by selecting manage data source.

That’s it guy’s, we have successfully integrated SSRS with SharePoint 2013. Click on any rdl file, it will redirect you to a report UI.

Summary

I walked you through the integration of SSRS with SharePoint 2013. I faced some issues while doing it but by following these steps you will not get any issues. If you face any problem while doing, you can contact me J

Happy reading. Have a great day ahead

You can write your feedback in comments below that will help me in improving myself. Thanks! Cheers J

Custom Workflow Activity

Create Custom Workflow Activity in SharePoint 2010

This blog demonstrates how to build the custom workflow activity in Visual Studio 2010 and use this workflow activity in SharePoint Designer 2010 as a custom action.

First you need to create the “Empty SharePoint Project” in Visual Studio 2010.

Please provide the URL of the SharePoint Site where you want to deploy the workflow. Also 

make sure that you select the “Deploy Solution as a farm solution”. Click next after that.

Before you begin you need to add the following reference to your project.

§  Microsoft.SharePoint.dll

§  Microsoft.Office.Workflow.Actions.dll

§  System.Workflow.ComponentModel.dll

There are two major steps to create a Custom action:

1.    Add a .class File

2.    Add WSS.actions file

Add a .class file

Add the new class to the solution and make sure that class is public otherwise you will get the “protection level error”. Inherit the class from Activity class. You must include using System.Workflow.ComponentModel to resolve the “Activity”.

Now you need to create the dependency properties to access the input given to the activity or to use the current library id and current item.

Definition: Dependency properties provide a centralized repository of a workflow’s state.Dependency Property provide a mechanism that allows SharePoint Designer to provide values for your custom action before you add your custom functionality.

To allow SharePoint Designer to pass values to your custom action when the workflow runs, you must create public properties inside your custom action.

The dependency properties can be created as:

/// <summary>

/// The dependency property for List ID.

/// </summary>

public static DependencyProperty __ListIdProperty = DependencyProperty.Register(“__ListId”, typeof (string), typeof(CustomWFAction));

Here CustomWFAction is a class name that we have created.

/// <summary>

/// Gets or sets the list or library ID.

/// </summary>

public string __ListId

{

get {

return ((string)this.GetValue(CustomWFAction.__ListIdProperty));

}

set {this.SetValue(CustomWFAction.__ListIdProperty, value); }

}

This property will provide you the current list or library id as a Guid on which workflow is running on. In the similar you can create required dependency properties and get/set their values. You can create a dependency property for Workflow Context, List litem id or any specific input that will be given to the workflow activity.

Now we are ready with the inputs to access the list or library with the current item ID on which workflow is running and the current context of the workflow (Create the dependency property for workflow context). We need to override the “Execute” method to execute the code.

The code for Execute () goes here.

/// <summary>

/// Write the actual code to be executed here.

/// </summary>

Protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)

{

    //Write the code here that is to be executed by activity.

    //Ex: Update the item title

 SPWeb web=__Context.Web;

 string listID= __ListId;

 int itemID = __ListItem;

 SPList lstDemo = web.Lists[new Guid(listID)];

 SPListItem lstItemDemo = lstDemo.GetItemById(itemID);

 if (lstDemo.Fields.ContainsField(“Title”))

{

  lstItemDemo[“Title”] = “Demo Activity executed”;

}

 lstItemDemo.Update();

 return base.Execute(executionContext);

}

Add WSS.actions file

Now we need to create the action file for the activity. Right click on the project and click on the add button and then select the “SharePoint Mapped Folder” which maps to workflow folder as shown below:

Now right click on the workflow folder and then add the new text file. Rename the text file with .actions extension.

Add the following code in the file and save it.

<? xml version=”1.0″ encoding=”utf-8″?>

<WorkflowInfo Language=”en-us”>

<Actions Sequential=”then” Parallel=”and”>

<Default>

<RuleDesigner Sentence=”Run action %1″>

<FieldBind Id=”1″ Function=”true”/>

</RuleDesigner>

</Default>

<Action Name=”UpdateItem” ClassName= ” My_CustomActivity.CustomWFAction”

Assembly=” IBSP_CustomActivity, Version=1.0.0.0, Culture=neutral, publicKeyToken = 87cb7fe4b26ece9b” Category=”SharePoint 2010 Actions” AppliesTo=”all” UsesCurrentItem=”true”>

<RuleDesigner Sentence=”Perform demo action”>

</RuleDesigner>

<Parameters>

<Parameter Name=”__Context” Type=”Microsoft.SharePoint.WorkflowActions.WorkflowContext, Microsoft.SharePoint.WorkflowActions” Direction=”In”/>

<Parameter Name=”__ListId” Type=”System.String, mscorlib” Direction=”In” />

<Parameter Name=”__ListItem” Type=”System.Int32, mscorlib” Direction=”In” />

</Parameters>

</Action>

</Actions>

</WorkflowInfo>

Deploy Custom Workflow Action

Now we have workflow activity ready with us. We now need to deploy this activity and use this activity using SharePoint Designer. Update the web.config file on the Site virtual directory folder where we need to create the workflow. Go to the “C:\inetpub\wwwroot\wss\VirtualDirectories\80” location and open the web.config file. Made the following entry to under the “<authorizedRuleTypes>” section.

<authorizedType Assembly=”System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ Namespace=”System.Runtime.CompilerServices” TypeName=”ExtensionAttribute” Authorized=”True” />

If you do not register it in web.config file, the action will be visible in workflow actions menu of SharePoint designer but will not get inserted into actual workflow. Restart the IIS after this step. Now Open the SharePoint designer 2010 and open the site where you want to create the workflow. Click on the “Workflows” from the left navigation bar and create the list workflow. Provide the Name and Description of the workflow. Then click on the action menu and now you will see the custom action under the “SharePoint 2010” group that we have created. Select the action and then publish the workflow.

Happy learning. J Have a great day.