Note that using this template is subject to the conditions of this License Agreement. Please review the terms of the license before downloading and using this template. In short, you are allowed to use the template for free with Mule ESB Enterprise Edition, CloudHub, or as a trial in Anypoint Studio.
As a Salesforce admin I want to aggregate users from a Salesforce Instances and a Database, and compare them to see which users can only be found in one of the two and which users are in both.
For practical purposes this Anypoint Template will generate the result in the format of a CSV Report sent by mail.
This Anypoint Template should serve as a foundation for extracting data from two systems, aggregating data, comparing values of fields for the objects, and generating a report on the differences.
As implemented, it gets users from a Salesforce instance and a DB table, compares by the email address of the users, and generates a CSV file which shows users in A, users in B (being "B" the DB), and Users in A and B. The report is then emailed to a configured group of email addresses.
To make this Anypoint Template run, there are certain preconditions that must be considered. All of them deal with the preparations in both, that must be made in order for all to run smoothly. Failling to do so could lead to unexpected behavior of the template.
Note: This particular Anypoint Template ilustrate the aggregation use case between SalesForce and a Data Base, thus it requires a DB instance to work. The Anypoint Template comes package with a SQL script to create the DB table that uses. It is the user responsability to use that script to create the table in an available schema and change the configuration accordingly. The SQL script file can be found in [src/main/resources/sfdc2jdbc.sql] (../master/src/main/resources/sfdc2jdbc.sql)
There may be a few things that you need to know regarding DB, in order for this template to work.
This Anypoint Template may be using date time/timestamp fields from the DB in order to do comparisons and take further actions. While the template handles the time zone by sending all such fields in a neutral time zone, it can not handle time offsets. We define as time offsets the time difference that may surface between date time/timestamp fields from different systems due to a differences in the system's internal clock. The user of this template should take this in consideration and take the actions needed to avoid the time offset.
There are no particular considerations for this Anypoint Template regarding DB as data destination.
There may be a few things that you need to know regarding Salesforce, in order for this template to work.
In order to have this template working as expected, you should be aware of your own Salesforce field configuration.
###FAQ
-
Where can I check that the field configuration for my Salesforce instance is the right one?
Salesforce: Checking Field Accessibility for a Particular Field
-
Can I modify the Field Access Settings? How?
If the user configured in the template for the source system does not have at least read only permissions for the fields that are fetched, then a InvalidFieldFault API fault will show up.
java.lang.RuntimeException: [InvalidFieldFault [ApiQueryFault [ApiFault exceptionCode='INVALID_FIELD'
exceptionMessage='
Account.Phone, Account.Rating, Account.RecordTypeId, Account.ShippingCity
^
ERROR at Row:1:Column:486
No such column 'RecordTypeId' on entity 'Account'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.'
]
row='1'
column='486'
]
]
Simple steps to get Salesforce and Database User Aggregation running.
In this section we detail the way you should run your Anypoint Template on your computer.
First thing to know if you are a newcomer to Mule is where to get the tools.
Mule Studio offers several ways to import a project into the workspace, for instance:
- Anypoint Studio generated Deployable Archive (.zip)
- Anypoint Studio Project from External Location
- Maven-based Mule Project from pom.xml
- Mule ESB Configuration XML from External Location
You can find a detailed description on how to do so in this Documentation Page.
Once you have imported you Anypoint Template into Anypoint Studio you need to follow these steps to run it:
- Locate the properties file
mule.dev.properties
, in src/main/resources - Complete all the properties required as per the examples in the section Properties to be configured
- Once that is done, right click on you Anypoint Template project folder
- Hover you mouse over
"Run as"
- Click on
"Mule Application"
Complete all properties in one of the property files, for example in [mule.prod.properties] (../master/src/main/resources/mule.prod.properties) and run your app with the corresponding environment variable to use it. To follow the example, this will be mule.env=prod
.
After this, to trigger the use case you just need to hit the local http endpoint with the port you configured in your file. If this is, for instance, 9090
then you should hit: http://localhost:9090/synccontacts
and this will create a CSV report and send it to the mails set.
While creating your application on CloudHub (Or you can do it later as a next step), you need to go to Deployment > Advanced to set all environment variables detailed in Properties to be configured as well as the mule.env.
Once your app is all set and started, supposing you choose as domain name useraggregation
to trigger the use case you just need to hit http://useraggregation.cloudhub.io/generatereport
and report will be sent to the emails configured.
Mule Studio provides you with really easy way to deploy your Template directly to CloudHub, for the specific steps to do so please check this link
In order to use this Mule Anypoint Template you need to configure properties (Credentials, configurations, etc.) either in properties file or in CloudHub as Environment Variables. Detail list with examples:
- http.port
9090
- sfdc.username
bob.dylan@orga
- sfdc.password
DylanPassword123
- sfdc.securityToken
avsfwCUl7apQs56Xq2AKi3X
- sfdc.url
https://login.salesforce.com/services/Soap/u/32.0
- db.jdbcUrl
jdbc:mysql://localhost:3306/sfdc2jdbc?rewriteBatchedStatements=true&user=root&password=password
- smtp.host
smtp.gmail.com
- smtp.port
587
- smtp.user
[email protected]
- smtp.password
ExamplePassword456
- mail.from
[email protected]
- mail.to
[email protected]
- mail.subject
SFDC Users Report
- mail.body
Users report comparing users from SFDC Accounts
- attachment.name
OrderedReport.csv
SalesForce imposes limits on the number of API Calls that can be made. Therefore calculating this amount may be an important factor to consider. User Anypoint Template calls to the API can be calculated using the formula:
1 + UsersToSync + UsersToSync / CommitSize
Being UsersToSync the number of Users to be synchronized on each run.
The division by CommitSize is because by default, for each Upsert API Call, Users are gathered in groups of a number defined by the Commit Size property. Also consider that this calls are executed repeatedly every polling cycle.
For instance if 10 records are fetched from origin instance, then 12 api calls will be made (1 + 10 + 1).
This brief guide intends to give a high level idea of how this Anypoint Template is built and how you can change it according to your needs. As mule applications are based on XML files, this page will be organized by describing all the XML that conform the Anypoint Template. Of course more files will be found such as Test Classes and Mule Application Files, but to keep it simple we will focus on the XMLs.
Here is a list of the main XML files you'll find in this application:
Configuration for Connectors and Properties Place Holders are set in this file. Even you can change the configuration here, all parameters that can be modified here are in properties file, and this is the recommended place to do it so. Of course if you want to do core changes to the logic you will probably need to modify this file.
In the visual editor they can be found on the Global Element tab.
Functional aspect of the Anypoint Template is implemented on this XML, directed by one flow responsible of conducting the aggregation of data, comparing records and finally formating the output, in this case being a report. The mainFlow organises the job in three different steps and finally invokes the outboundFlow that will deliver the report to the corresponding outbound endpoint. This flow has Exception Strategy that basically consists on invoking the defaultChoiseExceptionStrategy defined in errorHandling.xml file.
Mainly consisting of two calls (Queries) one to SalesForce and one to a DB and storing each response on the Invocation Variable named usersFromOrgA or usersFromDB accordingly.
Scatter Gather is responsible for aggregating the results from the two collections of Users. Criteria and format applied:
- Scatter Gather component implements an aggregation strategy that results in List of Maps with keys: Name, Email, IDInA, UserNameInA, IDInB and UserNameInB.
- Users will be matched by an email, that is to say, a record in both SFDC organisations with same mail is considered the same user.
- Java Transformer responsible for sorting the list of users in the following order:
- Users only in Org A
- Users only in Org B
- Users in both Org A and Org B
All records ordered alphabetically by mail within each category. If you want to change this order then the compare method should be modified.
- CSV Report DataWeave transforming the List of Maps in CSV with headers Name, Email, IDInA, UserNameInA, IDInB and UserNameInB.
- An Object to string transformer is used to set the payload as an String.
This is the file where you will found the inbound and outbound sides of your integration app. This Anypoint Template has an HTTP Inbound Endpoint as the way to trigger the use case and an SMTP Transport as the outbound way to send the report.
HTTP Inbound Endpoint - Start Report Generation
${http.port}
is set as a property to be defined either on a property file or in CloudHub environment variables.- The path configured by default is
generatereport
and you are free to change for the one you prefer. - The host name for all endpoints in your CloudHub configuration should be defined as
localhost
. CloudHub will then route requests from your application domain URL to the endpoint.
SMTP Outbound Endpoint - Send Mail
- Both SMTP Server configuration and the actual mail to be sent are defined in this endpoint.
- This flow is going to be invoked from the flow that does all the functional work: mainFlow, the same that is invoked from the Inbound Flow upon triggering of the HTTP Endpoint.
This is the right place to handle how your integration will react depending on the different exceptions. This file holds a Choice Exception Strategy that is referenced by the main flow in the business logic.