Back in 2006, Lightning Tools began developing BDC Meta Man which was considered (by us at least) as the saviour of the Business Data Catalog. It meant that you didn’t have to use notepad to write hundreds of lines of XML to connect to an SQL table!Since then, Microsoft SharePoint has gotten better and better, but so has BCS Meta Man. Using SharePoint Designer, you can now connect to Microsoft SQL Server databases without writing a bunch of XML.But with BCS Meta Man, you can connect to Microsoft SQL, Oracle, OData, WCF, Sage, Informix, DB2, MySQL and a whole load more without writing any code. We’re very pleased to announce that we have just added Salesforce into the mix. Yes, you can now use BCS Meta Man to connect to Salesforce!
BDC Meta Man building Application Definition Files for Microsoft SQL (Back in 2007)
BCS Meta Man building BDC Model files for Salesforce in 2014
Why use Business Connectivity Services to connect to Salesforce?
Business Connectivity Services is not the only option available to you to get data into SharePoint. However, it provides you with a wealth of features that many organizations do not consider. With Business Connectivity services, you are not just building a list with imported data which is what some third party tools provide. Business Connectivity Services provides:· Microsoft Office Integration of External Data· Business Data Web Parts· External List (Available with SharePoint Foundations)· User Profile Import· Enterprise Search· External Data Column· Client Object Model· Server Object ModelBusiness Connectivity Services is also an out-of-the-box service which means that you won’t get upgrade issues between SharePoint versions.The below screenshot shows Microsoft Word connecting to the Salesforce External Data via Business Connectivity Services and the External Data Column. While composing documents, users can insert quickparts to look up external data from Salesforce and store that data as Document meta data automatically.
Looking up a Salesforce Account from within Microsoft Word via Business Connectivity Services
Using the External List, you can view Salesforce data, as well as Create, Update, and Delete the Salesforce data. Some third party products import the data into a custom SharePoint list rather than link to it as with Business Connectivity Services. Importing the data inflates the content database unnecessarily and also causes synchronization issues. Using Business Connectivity Services, the data is linked so that updates are immediate via the External List.
The External List allow Create, Update, Delete functionality from within SharePoint on Salesforce dataThere are many more solutions that you can build with Business Connectivity Services using the web parts, search and user profile service.
Building the connection to Salesforce
Using BCS Meta Man, you can take the pain out of creating the connection in the first place. Below are the steps to getting Salesforce data into Microsoft SharePoint:
Building the Connected App within Salesforce
Any application that works with Salesforce will need to be trusted and authorised to connect to the Salesforce data. So, the first step is to create a Connected App.1. Login to Salesforce.com with your username and password.2. Click Setup in the top right hand corner3. Click Add an App4. Under Connected Apps, Click New5. Type a Connected App Name such as Meta Man.6. Enter an API Name such as Meta Man.7. Enter a Contact Email Address.8. Check Enable OAuth Settings.9. Enter a callback URL e.g. http://localhost/callback
10. Click Save11. You will now receive a Consumer Key and Consumer Secret which is required to connect via BCS Meta Man.
Setting up Secure Store for authentication with Salesforce
Using the Secure Store, we will setup two Secure Store applications to pass across your Salesforce Username and Password as well as the Consumer Key and Consumer Secret:1. Navigate to SharePoint Central Administration.2. Click Manage Service Applications under Application Management3. Click Secure Store Service4. Click New under Manage Target Applications5. Set a Target Application ID of Salesforce_User with a Display Name of Salesforce_User and enter a Contact Email address. Click Next.6. Set the first Field Type to Username7. Set the second Field Type to Password8. Click Next.9. Enter the Target Application Administrator and click OK.10. Create a second Target Application by clicking New under Manage Target Applications.11. Enter the Target Application ID of Salesforce_App.12. Set the display name to Salesforce_App13. Enter your email address as the contact email.14. Click Next15. Setup two fields as shown below:
12. Set the Target Application Administrator and Click OK.13. Select the Salesforce_App Target Application and choose ‘Set Credentials’14. Enter the Salesforce consumer key and consumer secret into the relevant fields.15. Select the Salesforce_User Target Application and choose ‘Set Credentials’16. Enter the Salesforce Username and Password into the relevant fields.
Start BCS Meta Man
Using BCS Meta Man, we will configure the Business Data Model File which contains our External Content Type, and then deploy the ECT to SharePoint.1. Click Salesforce under Data Source2. Complete the fields below:a. Salesforce Username (Used to configure the connection in the design instance)b. Salesforce Password (Used to configure the connection in the design instance)c. Consumer Key (Used to configure the connection in the design instance)d. Consumer Secret (Used to configure the connection in the design instance)3. Set the Secure Store ID which holds the user credentials to Salesforce_User4. Set the Secure Store ID which holds the consumer application credentials to Salesforce_App
Credentials configured for Salesforce within BCS Meta Man.5. Click the Blue Arrow to continue.6. Click Tables7. Drag the desired table onto the design surface8. Select the Finder, Specific Finder, Creator, Updater, and Deleter methods as desired
9. Click Create10. Click the icon in the top right hand corner of the table to preview the data.
11. Click Settings in the top right hand corner of BCS Meta Man.12. Provide the following fields:a. Model File Nameb. Model File Pathc. Model Named. Lob System Namee. Lob System Account Namef. Set the URL of the SharePoint Site within the SharePoint environment that you wish to deploy to.g. Click Save13. Click Deploy.14. Your External Content Type will now have been deployed to SharePoint. You may need to set permissions on your External Content Type within the Business Data Service Application settings.15. Within SharePoint, Add an External List and select your Salesforce External Content Type.