 |
VOOZH | about |
|
VOOZH | about |
ETL Validator provides data movement and transformation capabilities for integrating data platforms across your organization. CData's JDBC driver seamlessly integrates with ETL Validator and extends its native connectivity to include Salesforce data.
This tutorial explains how to build a simple ETL validator data flow to extract data from Salesforce data and load it into an example data storage solution: SQL Server.
Accessing and integrating live data from Salesforce has never been easier with CData. Customers rely on CData connectivity to:
Users frequently integrate Salesforce data with:
For more information on how CData solutions work with Salesforce, check out our Salesforce integration page.
CData extends ETL Validator's data connectivity capabilities by providing the ability to add data sources that connect via CData's JDBC drivers. Connecting to Salesforce data simply requires creating a new data source in ETL Validator through CData's connectiviy suite as described below.
Begin by logging into ETL Validator to view the application dashboard.
👁 Access the ETL Validator dashboardCData extends the data source options within ETL Validator.
👁 Create a new DataSourceCData's connectivity is embedded within ETL Validator's data source options.
👁 CData data sourceYou will need a JDBC connection string to establish a connection to Salesforce in ETL Validator.
👁 CData data sourceThere are several authentication methods available for connecting to Salesforce: OAuth, Login (or basic), and SSO. The Login method requires you to have the username, password, and security token of the user.
The default authentication mechanism (and the one preferred by Salesforce) is OAuth. To use OAuth with CData's embedded OAuth application, leave the connection properties blank. If you have configured your own custom OAuth application with Salesforce (see the Help documentation for more information), set OAuthClientId, OAuthClientSecret, and CallbackURL to the properties for you application. Set InitiateOAuth to the desired OAuth flow ("GETANDREFRESH" will have the connector manage the entire OAuth flow).
If you do not wish do not wish to use OAuth authentication, you can use Login (or basic) authentication. Set AuthScheme to Basic, and set the User, Password, and SecurityToken properties. You can configure your security token in Salesforce.
SSO (single sign-on) can be used by setting the SSOProperties, SSOLoginUrl, and SSOExchangeURL connection properties, which allow you to authenticate to an identity provider. See the "Getting Started" chapter in the Help documentation for more information.
If your Salesforce org has MFA enforcement enabled, set MFACode to the time-based one-time passcode (TOTP) generated by your authenticator app (such as Salesforce Authenticator or Google Authenticator). MFACode applies to both OAuth and Login authentication flows.
For assistance in constructing the JDBC URL, use the connection string designer built into the Salesforce JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.
java -jar cdata.jdbc.salesforce.jar
👁 Using the built-in connection string designer to generate a JDBC URL (salesforce is shown.)
A typical connection string looks like this:
jdbc:salesforce:InitiateOAuth=GETANDREFRESH;MFACode=YourMFACode
To ensure the JDBC driver is licensed appropriately, copy the license file to the appropriate location:
Copy the JDBC Driver for Salesforce and lic file from "C:\Program Files\CData[product_name]\lib" to "C:\Datagaps\ETLValidator\Server\apache-tomcat\bin".
cdata.jdbc.salesforce.jar cdata.jdbc.salesforce.lic
Note: If you do not copy the .lic file with the jar, you will see a licensing error that indicates you do not have a valid license installed. This is true for both the trial and full versions.
Should you encounter any difficulties loading the CData JDBC driver class, please contact DataGap's team, and they will provide you instructions on how to load the jar file for the relevant driver.
This example will use SQL Server as a destination for Salesforce data data, but any preferred destination can be used instead.
This option is the default.
👁 Add SQL ServerThe details will depend on the specific target, but these details may include a URL, authentiation credentials, etc.
👁 Add SQL ServerConfigured data flows will appear in this window.
👁 Dataflows tabName your new dataflow and save it.
The details of the data movement will be configured in this panel.
👁 Dataflow diagramGive the new source an appropriate name and save it.
👁 Jira example sourceSelect the Table from the Schema option that reflects which data should be pulled from Salesforce data.
The anticipated outcome of the configured query is displayed in the Result tab.
👁 Query resultsSelect Switch to Diagram, then drag & drop the DB Sink as a target from the right side (under Sink options). Give the sink an appropriate name and save it.
👁 Data sinkChoose the Schema and table that matches the structure of the source table. For this example, the table on the target side was created to match the Source so that data flow seamlessly. More advanced schema transformation operations are beyond the scope of this article.
👁 Destination schemaRunning the job will take some time.
👁 Destination schemaReturn to the diagram to see the finished data replication job from Salesforce data to SQL Server.
👁 Full dataflowDownload a free, 30-day trial of the CData JDBC Driver for Salesforce and start building Salesforce-connected applications with ETL Validator. Reach out to our Support Team if you have any questions.
Download a free trial of the Salesforce Driver to get started:
Download NowLearn more:
👁 Salesforce IconRapidly create and deploy powerful Java applications that integrate with Salesforce account data including Leads, Contacts, Opportunities, Accounts, and more!
