Creating a Salesforce External Data Source using an OData Service Created in .Net

(Puedes ver este artículo en español aquí)

Salesforce allows to access external data sources using OData protocol. This feature is actually one of the most useful feature when designing and integration strategy with backend systems. Instead of using web services or the DataLoader to put the data into Salesforce, we can get up-to-date data directly from the third-party system by using an external data source. By using an external data source, and the external object it creates, we can access information located outside Salesforce as if it were stored inside Salesforce. The only disadvantage at the moment is that an external data source is read only, so we cannot use it to update data on third-party system. But still, this opens a whole lot of integration scenarios for us.

Let’s see an example of when this might come useful. I recently worked on a project where an integration between Salesforce and the ERP was required. Sales executives needed to see Account administrative alerts (e.g. “client has pending bills”, or “client has a claim on its latest bill”, etc.) in Salesforce. These alerts were generated in the ERP by the administrative users. Clearly we needed to bring those alerts to Salesforce.

One approach would be to use the DataLoader to upload alerts to Salesforce in batch, with a predefined frequency (once a day for example). This solution is quick and easy, but requires a lot of steps: creating the Alerts object in Salesforce, setting up the DataLoader command line mode to retrieve the data directly from the ERP and do an upsert in Salesforce, and using a scheduler program to do execute the DataLoader job at regular intervals. But it turns out there is an easier way! Well, easier if you read this post :-)

Instead of putting the data into Salesforce, we will let Salesforce retrieve the data dynamically from the ERP. For this we will use an external data source and create an external object. You can create an external object based in OData (unfortunately only version 2.0, although at the time of writing this OData is at version 4.0). Let’s see how we can use Visual Studio and .Net to expose the data from the ERP.

Setting up the Example

I will use a pretty simple example. I will simulate the ERP as a SQLServer database. We have an account table and an alert table that defines the alerts for our accounts. Our ERP will look like this:

Use the following script to create the database and the tables:

/****** Object:  Database [ERP]    Script Date: 12/30/2014 17:22:48 ******/
CREATE DATABASE [ERP] ON  PRIMARY 
( NAME = N'ERP', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ERP.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'ERP_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ERP_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

USE [ERP]

/****** Object:  Table [dbo].[Account]    Script Date: 12/30/2014 17:22:49 ******/
CREATE TABLE [dbo].[Account](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Alert]    Script Date: 12/30/2014 17:22:49 ******/
CREATE TABLE [dbo].[Alert](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [accountId] [int] NOT NULL,
    [alertText] [varchar](50) NULL,
 CONSTRAINT [PK_Alert] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  ForeignKey [FK_Alert_Account]    Script Date: 12/30/2014 17:22:49 ******/
ALTER TABLE [dbo].[Alert]  WITH CHECK ADD  CONSTRAINT [FK_Alert_Account] FOREIGN KEY([accountId])
REFERENCES [dbo].[Account] ([id])
GO
ALTER TABLE [dbo].[Alert] CHECK CONSTRAINT [FK_Alert_Account]
GO

And populate the database with the following script:

insert into Account values('ModicaTech')
insert into Account values('Microsoft')
insert into Account values('Salesforce')
insert into Alert values(1,'Client requesting support')
insert into Alert values(2,'Client has pending receipts')

Creating the OData Web Service

Using Visual Studio 2013 Community edition, create a new empty ASP.Net Web project. We will add an Entity Framework data model to this project. Right click on the project name and choose “Add New Item”. Select “ADO.ET Entity Data Model” and name it ErpModel.

On the Entity Data Model Wizard, select “EF Designer from database” and click Next. Create a new connection to the SQLServer and the ERP database:

Click Next and make sure you select “Entity Framework 5.0” as the version of Entity Framework to use, and click Next. Select both tables:

Click Finish. The Wizard will generate the code to connect to the database. We will now add the OData service to the project. Right click on the project name and choose “Add New Item…". Select “WCF Data Service 5.6” and name it ErpService.svc:

The wizard will create the ErpService.svc.cs file and open it on the editor. Replace the text in the TODO for the name of the entity data model. The file should look like this:

using System;
using System.Collections.Generic;
using System.Data.Services;
using System.Data.Services.Common;
using System.Linq;
using System.ServiceModel.Web;
using System.Web;

namespace ErpODataService
{
    public class ErpService : DataService<ERPEntities>
    {
        // This method is called only once to initialize service-wide policies.
        public static void InitializeService(DataServiceConfiguration config)
        {
            config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
            // config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
            config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
        }
    }
}

Notice line 11 where we specify the name of our entity data model. Line 16 specifies read access for all our entities. Finally, line 18 sets the version of OData to 2.0 (the one required by Salesforce). If you run the project you should see something like this:

Publish the web service to a public internet URL. I have published in an IIS in our DMZ with the URL http://www.grupolanka.com/Salesforce/ErpODataService/ErpService.svc (don’t try, it wont work)

Creating the External Data Source in Salesforce

Go to Salesforce, then go to “Setup->Build->Develop->External Data Sources” and click on the “New External Data Source” button. Specify a label and name and make sure you select “Lighting Connect: OData 2.0” as the type. In the server url put the url to access the OData service we created, and in the format select AtomPub. Specify anonymous as the authentication since we haven’t specify any security on the OData service (for simplicity). The external data source should like like the following:

When you save it, you will click on “Validate and Sync” button:

Select the Alerts object and click the Sync button. Salesforce will create the Alerts__x object. Click on it to see its properties:

We will now create a relationship between this external object and the Account object to link alerts to accounts. But first we need to create a custom field on Account and mark it as an external id in order to make the relationship. Go to “Setup->Build->Customize->Accounts->Fields“ and in the “Account Custom Fields & Relationships” create a new custom field called External_ID. Make sure to mark it as “Unique” and “External ID”:

Go to back to the Alerts external object (“Setup->Build->Develop->External Objects”). On the “Custom Fields & Relationships” section click on New. Choose “Indirect Lookup Relationship” as the field data type. Next select Accounts in the “Related To” field:

Click Next. In the target field select custom field for account we created before (External_ID__c)

Click Next. Enter a label and name for the field. Make sure you specify accountId as the “External Column Name”. This is the field in the external data source that contains the foreign key to account.

Click Next on the rest of the Wizard.

We now need to create a Tab for the new object and add it to an application. Go to “Setup->Build->Create->Tabs” and create a new tab for the Alerts object. Select a theme. Then assign the tab to an application. You should now see something like this:

Click on one of the records to see its details:

Seeing the External Data on a Salesforce Object

Now, let’s open an account (any account). Specify a value for the field “External Id”. Use 1 as the value so we will get all the alerts associated to the account with id 1

Notice that you will need to edit the layout for the related list to show the alertText field.

And this is it! We can dynamically see the data from our ERP in Salesforce. Let’s do a test. Insert a new alert for the account with id 1:

insert into Alert values(1,'This thing works!')

Refresh the account in Salesforce, you should see the new alert:

You can find the Visual Studio project here: