Copy data from and to Salesforce - Azure Data Factory & Azure Synapse (2023)

APPLIES TO: Azure Data Factory Azure Synapse Analytics

This article outlines how to use Copy Activity in Azure Data Factory and Azure Synapse pipelines to copy data from and to Salesforce. It builds on the Copy Activity overview article that presents a general overview of the copy activity.

Supported capabilities

This Salesforce connector is supported for the following capabilities:

① Azure integration runtime ② Self-hosted integration runtime

For a list of data stores that are supported as sources or sinks, see the Supported data stores table.

Specifically, this Salesforce connector supports:

• Salesforce Developer, Professional, Enterprise, or Unlimited editions.
• Copying data from and to Salesforce production, sandbox, and custom domain.

The Salesforce connector is built on top of the Salesforce REST/Bulk API. When copying data from Salesforce, the connector automatically chooses between REST and Bulk APIs based on the data size – when the result set is large, Bulk API is used for better performance; You can explicitly set the API version used to read/write data via apiVersion property in linked service. When copying data to Salesforce, the connector uses BULK API v1.

Prerequisites

API permission must be enabled in Salesforce.

Salesforce request limits

Salesforce has limits for both total API requests and concurrent API requests. Note the following points:

• If the number of concurrent requests exceeds the limit, throttling occurs and you see random failures.
• If the total number of requests exceeds the limit, the Salesforce account is blocked for 24 hours.

You might also receive the "REQUEST_LIMIT_EXCEEDED" error message in both scenarios. For more information, see the "API request limits" section in Salesforce developer limits.

Get started

To perform the Copy activity with a pipeline, you can use one of the following tools or SDKs:

• The Copy Data tool
• The Azure portal
• The .NET SDK
• The Python SDK
• Azure PowerShell
• The REST API
• The Azure Resource Manager template

Create a linked service to Salesforce using UI

Use the following steps to create a linked service to Salesforce in the Azure portal UI.

1. Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then click New:

   • Azure Data Factory
   • Azure Synapse

   

2. Search for Salesforce and select the Salesforce connector.

   

   (Video) Extract Salesforce to Azure Data Lake using Azure Data Factory

3. Configure the service details, test the connection, and create the new linked service.

   

Connector configuration details

The following sections provide details about properties that are used to define entities specific to the Salesforce connector.

Linked service properties

The following properties are supported for the Salesforce linked service.

Example: Store credentials

{ "name": "SalesforceLinkedService", "properties": { "type": "Salesforce", "typeProperties": { "username": "<username>", "password": { "type": "SecureString", "value": "<password>" }, "securityToken": { "type": "SecureString", "value": "<security token>" } }, "connectVia": { "referenceName": "<name of Integration Runtime>", "type": "IntegrationRuntimeReference" } }}

Example: Store credentials in Key Vault

{ "name": "SalesforceLinkedService", "properties": { "type": "Salesforce", "typeProperties": { "username": "<username>", "password": { "type": "AzureKeyVaultSecret", "secretName": "<secret name of password in AKV>", "store":{ "referenceName": "<Azure Key Vault linked service>", "type": "LinkedServiceReference" } }, "securityToken": { "type": "AzureKeyVaultSecret", "secretName": "<secret name of security token in AKV>", "store":{ "referenceName": "<Azure Key Vault linked service>", "type": "LinkedServiceReference" } } }, "connectVia": { "referenceName": "<name of Integration Runtime>", "type": "IntegrationRuntimeReference" } }}

Dataset properties

For a full list of sections and properties available for defining datasets, see the Datasets article. This section provides a list of properties supported by the Salesforce dataset.

To copy data from and to Salesforce, set the type property of the dataset to SalesforceObject. The following properties are supported.

Example:

{ "name": "SalesforceDataset", "properties": { "type": "SalesforceObject", "typeProperties": { "objectApiName": "MyTable__c" }, "schema": [], "linkedServiceName": { "referenceName": "<Salesforce linked service name>", "type": "LinkedServiceReference" } }}

Copy activity properties

For a full list of sections and properties available for defining activities, see the Pipelines article. This section provides a list of properties supported by Salesforce source and sink.

Salesforce as a source type

To copy data from Salesforce, set the source type in the copy activity to SalesforceSource. The following properties are supported in the copy activity source section.

Example:

"activities":[ { "name": "CopyFromSalesforce", "type": "Copy", "inputs": [ { "referenceName": "<Salesforce input dataset name>", "type": "DatasetReference" } ], "outputs": [ { "referenceName": "<output dataset name>", "type": "DatasetReference" } ], "typeProperties": { "source": { "type": "SalesforceSource", "query": "SELECT Col_Currency__c, Col_Date__c, Col_Email__c FROM AllDataType__c" }, "sink": { "type": "<sink type>" } } }]

Salesforce as a sink type

To copy data to Salesforce, set the sink type in the copy activity to SalesforceSink. The following properties are supported in the copy activity sink section.

Example: Salesforce sink in a copy activity

"activities":[ { "name": "CopyToSalesforce", "type": "Copy", "inputs": [ { "referenceName": "<input dataset name>", "type": "DatasetReference" } ], "outputs": [ { "referenceName": "<Salesforce output dataset name>", "type": "DatasetReference" } ], "typeProperties": { "source": { "type": "<source type>" }, "sink": { "type": "SalesforceSink", "writeBehavior": "Upsert", "externalIdFieldName": "CustomerId__c", "writeBatchSize": 10000, "ignoreNullValues": true } } }]

Query tips

Retrieve data from a Salesforce report

You can retrieve data from Salesforce reports by specifying a query as {call "<report name>"}. An example is "query": "{call \"TestReport\"}".

Retrieve deleted records from the Salesforce Recycle Bin

To query the soft deleted records from the Salesforce Recycle Bin, you can specify readBehavior as queryAll.

Difference between SOQL and SQL query syntax

When copying data from Salesforce, you can use either SOQL query or SQL query. Note that these two has different syntax and functionality support, do not mix it. You are suggested to use the SOQL query, which is natively supported by Salesforce. The following table lists the main differences:

Retrieve data by using a where clause on the DateTime column

When you specify the SOQL or SQL query, pay attention to the DateTime format difference. For example:

• SOQL sample: SELECT Id, Name, BillingCity FROM Account WHERE LastModifiedDate >= @{formatDateTime(pipeline().parameters.StartTime,'yyyy-MM-ddTHH:mm:ssZ')} AND LastModifiedDate < @{formatDateTime(pipeline().parameters.EndTime,'yyyy-MM-ddTHH:mm:ssZ')}
• SQL sample: SELECT * FROM Account WHERE LastModifiedDate >= {ts'@{formatDateTime(pipeline().parameters.StartTime,'yyyy-MM-dd HH:mm:ss')}'} AND LastModifiedDate < {ts'@{formatDateTime(pipeline().parameters.EndTime,'yyyy-MM-dd HH:mm:ss')}'}

Error of MALFORMED_QUERY: Truncated

If you hit error of "MALFORMED_QUERY: Truncated", normally it's due to you have JunctionIdList type column in data and Salesforce has limitation on supporting such data with large number of rows. To mitigate, try to exclude JunctionIdList column or limit the number of rows to copy (you can partition to multiple copy activity runs).

Data type mapping for Salesforce

When you copy data from Salesforce, the following mappings are used from Salesforce data types to interim data types within the service internally. To learn about how the copy activity maps the source schema and data type to the sink, see Schema and data type mappings.

Lookup activity properties

To learn details about the properties, check Lookup activity.

Next steps

For a list of data stores supported as sources and sinks by the copy activity, see Supported data stores.