Copy data from and to Salesforce by using Azure Data Factory

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

Note

This article applies to version 2 of Data Factory, which is currently in preview. If you use version 1 of Data Factory, which is generally available, see Salesforce connector in version 1.

Supported capabilities

You can copy data from Salesforce to any supported sink data store. You also can copy data from any supported source data store to Salesforce. For a list of data stores that are supported as sources or sinks by the Copy activity, 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.

Prerequisites

API permission must be enabled in Salesforce. For more information, see Enable API access in Salesforce by permission set

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

You can create a pipeline with the copy activity by using one of the following tools or SDKs. Select a link to go to a tutorial with step-by-step instructions to create a pipeline with a copy activity.

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

Linked service properties

The following properties are supported for the Salesforce linked service.

Property Description Required
type The type property must be set to Salesforce. Yes
environmentUrl Specify the URL of the Salesforce instance.
- Default is "https://login.salesforce.com".
- To copy data from sandbox, specify "https://test.salesforce.com".
- To copy data from custom domain, specify, for example, "https://[domain].my.salesforce.com".
No
username Specify a user name for the user account. Yes
password Specify a password for the user account.

Mark this field as a SecureString to store it securely in Data Factory, or reference a secret stored in Azure Key Vault.
Yes
securityToken Specify a security token for the user account. For instructions on how to reset and get a security token, see Get a security token. To learn about security tokens in general, see Security and the API.

Mark this field as a SecureString to store it securely in Data Factory, or reference a secret stored in Azure Key Vault.
Yes
connectVia The integration runtime to be used to connect to the data store. If not specified, it uses the default Azure Integration Runtime. No for source, Yes for sink if the source linked service doesn't have integration runtime

Important

When you copy data into Salesforce, the default Azure Integration Runtime can't be used to execute copy. In other words, if your source linked service doesn't have a specified integration runtime, explicitly create an Azure Integration Runtime with a location near your Salesforce instance. Associate the Salesforce linked service as in the following example.

Example: Store credentials in Data Factory

{
    "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.

Property Description Required
type The type property must be set to SalesforceObject. Yes
objectApiName The Salesforce object name to retrieve data from. No for source, Yes for sink

Important

The "__c" part of API Name is needed for any custom object.

Data Factory Salesforce connection API Name

Example:

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

Note

For backward compatibility: When you copy data from Salesforce, if you use the previous "RelationalTable" type dataset, it keeps working while you see a suggestion to switch to the new "SalesforceObject" type.

Property Description Required
type The type property of the dataset must be set to RelationalTable. Yes
tableName Name of the table in Salesforce. No (if "query" in the activity source is specified)

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.

Property Description Required
type The type property of the copy activity source must be set to SalesforceSource. Yes
query Use the custom query to read data. You can use a SQL-92 query or Salesforce Object Query Language (SOQL) query. An example is select * from MyTable__c. No (if "tableName" in the dataset is specified)

Important

The "__c" part of API Name is needed for any custom object.

Data Factory Salesforce connection API Name list

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>"
            }
        }
    }
]

Note

For backward compatibility: When you copy data from Salesforce, if you use the previous "RelationalSource" type copy, the source keeps working while you see a suggestion to switch to the new "SalesforceSource" 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.

Property Description Required
type The type property of the copy activity sink must be set to SalesforceSink. Yes
writeBehavior The write behavior for the operation.
Allowed values are Insert and Upsert.
No (default is Insert)
externalIdFieldName The name of the external ID field for the upsert operation. The specified field must be defined as "External Id Field" in the Salesforce object. It can't have NULL values in the corresponding input data. Yes for "Upsert"
writeBatchSize The row count of data written to Salesforce in each batch. No (default is 5,000)
ignoreNullValues Indicates whether to ignore NULL values from input data during a write operation.
Allowed values are true and false.
- True: Leave the data in the destination object unchanged when you do an upsert or update operation. Insert a defined default value when you do an insert operation.
- False: Update the data in the destination object to NULL when you do an upsert or update operation. Insert a NULL value when you do an insert operation.
No (default is false)

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 "IsDeleted = 1" in your query. For example:

  • To query only the deleted records, specify "select * from MyTable__c where IsDeleted= 1."
  • To query all the records, including the existing and the deleted, specify "select * from MyTable__c where IsDeleted = 0 or IsDeleted = 1."

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')}'}"

Data type mapping for Salesforce

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

Salesforce data type Data Factory interim data type
Auto Number String
Checkbox Boolean
Currency Double
Date DateTime
Date/Time DateTime
Email String
Id String
Lookup Relationship String
Multi-Select Picklist String
Number Double
Percent Double
Phone String
Picklist String
Text String
Text Area String
Text Area (Long) String
Text Area (Rich) String
Text (Encrypted) String
URL String

Next steps

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