Copy data from and to Salesforce V2 using Azure Data Factory or Azure Synapse Analytics

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 custom domain (Custom domain can be configured in both production and sandbox environments).

You can explicitly set the API version used to read/write data via apiVersion property in linked service. When you are copying data to Salesforce, the connector uses BULK API 2.0.

Prerequisites

• API permission must be enabled in Salesforce.

• You need configure the Connected Apps in Salesforce portal referring to this official doc or our step by step guideline in the recommendation in this article.

  Important

  • The execution user must have the API Only permission.
  • Access Token expire time could be changed through session policies instead of the refresh token.

Salesforce Bulk API 2.0 Limits

We use Salesforce Bulk API 2.0 to query and ingest data. In Bulk API 2.0, batches are created for you automatically. You can submit up to 15,000 batches per rolling 24-hour period. If batches exceed the limit, you encounter failures.

In Bulk API 2.0, only ingest jobs consume batches. Query jobs don't. For details, see How Requests Are Processed in the Bulk API 2.0 Developer Guide.

For more information, see the "General 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:

• Copy Data tool
• Azure portal
• .NET SDK
• Python SDK
• Azure PowerShell
• REST API
• 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.

   

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": "SalesforceV2",
        "typeProperties": {
            "environmentUrl": "",
            "authenticationType": "OAuth2ClientCredentials",
            "clientId": "",
            "clientSecret": {
                "type": "SecureString",
                "value": ""
            },
            "apiVersion": ""
        },
        "connectVia": {
            "referenceName": "",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Example: Store credentials in Key Vault

{
    "name": "SalesforceLinkedService",
    "properties": {
        "type": "SalesforceV2",
        "typeProperties": {
            "environmentUrl": "",
            "authenticationType": "OAuth2ClientCredentials",
            "clientId": "",
            "clientSecret": {
                "type": "AzureKeyVaultSecret",
                "secretName": "",
                "store":{
                    "referenceName": "",
                    "type": "LinkedServiceReference"
                }
            },
            "apiVersion": ""
        },
        "connectVia": {
            "referenceName": "",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Example: Store credentials in Key Vault, as well as environmentUrl and clientId

By doing storing credentials in Key Vault, as well as environmentUrl and clientId, you can longer use the UI to edit settings. The Specify dynamic contents in JSON format checkbox must be checked, and you must this configuration manually. The advantage of this scenario is that you can derive all configuration settings from the Key Vault instead of parameterizing anything here.

{
    "name": "SalesforceLinkedService",
    "properties": {
        "type": "SalesforceV2",
        "typeProperties": {
            "environmentUrl": {
                "type": "AzureKeyVaultSecret",
                "secretName": "",
                "store": {
                    "referenceName": "",
                    "type": "LinkedServiceReference"
                },
            },
            "authenticationType": "OAuth2ClientCredentials",
            "clientId": {
                "type": "AzureKeyVaultSecret",
                "secretName": "",
                "store": {
                    "referenceName": "",
                    "type": "LinkedServiceReference"
                },
            },
            "clientSecret": {
                "type": "AzureKeyVaultSecret",
                "secretName": "",
                "store":{
                    "referenceName": "",
                    "type": "LinkedServiceReference"
                }
            },
            "apiVersion": ""
        },
        "connectVia": {
            "referenceName": "",
            "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 SalesforceV2Object. The following properties are supported.

Example:

{
    "name": "SalesforceDataset",
    "properties": {
        "type": "SalesforceV2Object",
        "typeProperties": {
            "objectApiName": "MyTable__c"
        },
        "schema": [],
        "linkedServiceName": {
            "referenceName": "",
            "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 SalesforceV2Source. The following properties are supported in the copy activity source section.

Example:

"activities":[
    {
        "name": "CopyFromSalesforce",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "
",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SalesforceV2Source",
                "query": "SELECT Col_Currency__c, Col_Date__c, Col_Email__c FROM AllDataType__c",
                "includeDeletedObjects": false,
                "preserveScaleFromSchema": false,
                "partitionOption": "AutoDetect"
            },
            "sink": {
                "type": ""
            }
        }
    }
]

Salesforce as a sink type

To copy data to Salesforce, set the sink type in the copy activity to SalesforceV2Sink. 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": "",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": ""
            },
            "sink": {
                "type": "SalesforceV2Sink",
                "writeBehavior": "Upsert",
                "externalIdFieldName": "CustomerId__c",
                "writeBatchSize": 10000,
                "ignoreNullValues": true
            }
        }
    }
]

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.

Salesforce connector lifecycle and upgrade

The following table shows the release stage and change logs for different versions of the Salesforce connector:

Upgrade the Salesforce connector from V1 to V2

Here are steps that help you upgrade your Salesforce connector:

1. Configure the connected apps in Salesforce portal by referring to Prerequisites.

2. Create a new Salesforce linked service and configure it by referring to Linked service properties. You also need to manually update existing datasets that rely on the old linked service, editing each dataset to use the new linked service instead.

3. If you use SQL query in the copy activity source or the lookup activity that refers to the V1 linked service, you need to convert them to the SOQL query. Learn more about SOQL query from Salesforce as a source type and Salesforce Object Query Language (SOQL).

4. Support partitionOption in the copy activity source. For the detailed configuration, see Salesforce as a source type.

5. Support preserveScaleFromSchema in the copy activity source. For the detailed configuration, see Salesforce as a source type.

6. readBehavior is replaced with includeDeletedObjects in the copy activity source or the lookup activity. For the detailed configuration, see Salesforce as a source type.

Related content

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