使用 Azure 数据工厂从/向 Salesforce Service Cloud 复制数据Copy data from and to Salesforce Service Cloud by using Azure Data Factory

适用于:是 Azure 数据工厂是 Azure Synapse Analytics(预览版)APPLIES TO: yesAzure Data Factory yesAzure Synapse Analytics (Preview)

本文概述如何使用 Azure 数据工厂中的复制活动从/向 Salesforce Service Cloud 复制数据。This article outlines how to use Copy Activity in Azure Data Factory to copy data from and to Salesforce Service Cloud. 本文基于总体概述复制活动的复制活动概述一文。It builds on the Copy Activity overview article that presents a general overview of the copy activity.

支持的功能Supported capabilities

以下活动支持此 Salesforce Service Cloud 连接器:This Salesforce Service Cloud connector is supported for the following activities:

可将数据从 Salesforce Service Cloud 复制到任何支持的接收器数据存储。You can copy data from Salesforce Service Cloud to any supported sink data store. 还可将数据从任何支持的源数据存储复制到 Salesforce Service Cloud。You also can copy data from any supported source data store to Salesforce Service Cloud. 有关复制活动支持作为源或接收器的数据存储列表,请参阅支持的数据存储表。For a list of data stores that are supported as sources or sinks by the Copy activity, see the Supported data stores table.

具体而言,Salesforce Service Cloud 连接器支持:Specifically, this Salesforce Service Cloud connector supports:

  • Salesforce 开发人员版、专业版、企业版或不受限制版。Salesforce Developer, Professional, Enterprise, or Unlimited editions.
  • 从/向 Salesforce 生产、沙盒和自定义域复制数据。Copying data from and to Salesforce production, sandbox, and custom domain.

Salesforce 连接器在 Salesforce REST/Bulk API 之上构建。The Salesforce connector is built on top of the Salesforce REST/Bulk API. 默认情况下,连接器使用 v45 从 Salesforce 复制数据,使用 v40 将数据复制到 Salesforce。By default, the connector uses v45 to copy data from Salesforce, and uses v40 to copy data to Salesforce. 还可通过链接服务中的 apiVersion 属性显式设置用于读取/写入数据的 API 版本。You can also explicitly set the API version used to read/write data via apiVersion property in linked service.

先决条件Prerequisites

在 Salesforce 中,必须启用 API 权限。API permission must be enabled in Salesforce. 有关详细信息,请参阅通过权限集启用 Salesforce 中的 API 访问权限For more information, see Enable API access in Salesforce by permission set

Salesforce 请求限制Salesforce request limits

Salesforce 对 API 请求总数和并发 API 请求均有限制。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.
  • 如果请求总数超过限制,会阻止 Salesforce 帐户 24 小时。If the total number of requests exceeds the limit, the Salesforce account is blocked for 24 hours.

在这两种情况下,还可能会收到“REQUEST_LIMIT_EXCEEDED”错误消息。You might also receive the "REQUEST_LIMIT_EXCEEDED" error message in both scenarios. 有关详细信息,请参阅 Salesforce 开发人员限制中的“API 请求限制”部分。For more information, see the "API request limits" section in Salesforce developer limits.

入门Get started

若要使用管道执行复制活动,可以使用以下工具或 SDK 之一:To perform the Copy activity with a pipeline, you can use one of the following tools or SDKs:

对于特定于 Salesforce Service Cloud 连接器的数据工厂实体,以下部分提供有关用于定义这些实体的属性详细信息。The following sections provide details about properties that are used to define Data Factory entities specific to the Salesforce Service Cloud connector.

链接服务属性Linked service properties

Salesforce 链接服务支持以下属性。The following properties are supported for the Salesforce linked service.

属性Property 说明Description 必须Required
typetype type 属性必须设置为 SalesforceServiceCloudThe type property must be set to SalesforceServiceCloud. Yes
environmentUrlenvironmentUrl 指定 Salesforce Service Cloud 实例的 URL。Specify the URL of the Salesforce Service Cloud instance.
- 默认为 "https://login.salesforce.com"- Default is "https://login.salesforce.com".
- 要从沙盒复制数据,请指定 "https://test.salesforce.com"- To copy data from sandbox, specify "https://test.salesforce.com".
- 要从自定义域复制数据,请指定 "https://[domain].my.salesforce.com"(以此为例)。- To copy data from custom domain, specify, for example, "https://[domain].my.salesforce.com".
No
usernameusername 为用户帐户指定用户名。Specify a user name for the user account. Yes
passwordpassword 指定用户帐户的密码。Specify a password for the user account.

将此字段标记为 SecureString 以安全地将其存储在数据工厂中或引用存储在 Azure Key Vault 中的机密Mark this field as a SecureString to store it securely in Data Factory, or reference a secret stored in Azure Key Vault.
Yes
securityTokensecurityToken 为用户帐户指定安全令牌。Specify a security token for the user account.

若要了解有关安全令牌的一般信息,请参阅 Security and the API(安全性和 API)。To learn about security tokens in general, see Security and the API. 仅当将 Integration Runtime 的 IP 添加到 Salesforce 上的受信任 IP 地址列表时,才能跳过安全令牌。The security token can be skipped only if you add the Integration Runtime's IP to the trusted IP address list on Salesforce. 使用 Azure IR 时,请参阅 Azure Integration Runtime IP 地址When using Azure IR, refer to Azure Integration Runtime IP addresses.

有关如何获取和重置安全令牌的说明,请参阅获取安全令牌For instructions on how to get and reset a security token, see Get a security token. 将此字段标记为 SecureString 以安全地将其存储在数据工厂中或引用存储在 Azure Key Vault 中的机密Mark this field as a SecureString to store it securely in Data Factory, or reference a secret stored in Azure Key Vault.
No
apiVersionapiVersion 指定要使用的 Salesforce REST/Bulk API 版本,例如 48.0Specify the Salesforce REST/Bulk API version to use, e.g. 48.0. 默认情况下,连接器使用 v45 从 Salesforce 复制数据,使用 v40 将数据复制到 Salesforce。By default, the connector uses v45 to copy data from Salesforce, and uses v40 to copy data to Salesforce. No
connectViaconnectVia 用于连接到数据存储的集成运行时The integration runtime to be used to connect to the data store. 如果未指定,则使用默认 Azure Integration Runtime。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

重要

将数据复制到 Salesforce Service Cloud 时,不能使用默认 Azure 集成运行时执行复制。When you copy data into Salesforce Service Cloud, the default Azure Integration Runtime can't be used to execute copy. 换而言之,如果源链接服务未指定集成运行时,请使用靠近 Salesforce Service Cloud 实例的位置显式创建 Azure 集成运行时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 Service Cloud instance. 按以下示例所示关联 Salesforce Service Cloud 链接服务。Associate the Salesforce Service Cloud linked service as in the following example.

示例:在数据工厂中存储凭据Example: Store credentials in Data Factory

{
    "name": "SalesforceServiceCloudLinkedService",
    "properties": {
        "type": "SalesforceServiceCloud",
        "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": "SalesforceServiceCloudLinkedService",
    "properties": {
        "type": "SalesforceServiceCloud",
        "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. 本部分提供 Salesforce Service Cloud 数据集支持的属性列表。This section provides a list of properties supported by the Salesforce Service Cloud dataset.

支持使用以下属性从/向 Salesforce Service Cloud 复制数据。To copy data from and to Salesforce Service Cloud, the following properties are supported.

属性Property 说明Description 必须Required
typetype type 属性必须设置为 SalesforceServiceCloudObjectThe type property must be set to SalesforceServiceCloudObject. Yes
objectApiNameobjectApiName 要从中检索数据的 Salesforce 对象名称。The Salesforce object name to retrieve data from. 对于源为“No”,对于接收器为“Yes”No for source, Yes for sink

重要

任何自定义对象均需要 API 名称的“__c”部分。The "__c" part of API Name is needed for any custom object.

数据工厂 Salesforce 连接 API 名称

示例:Example:

{
    "name": "SalesforceServiceCloudDataset",
    "properties": {
        "type": "SalesforceServiceCloudObject",
        "typeProperties": {
            "objectApiName": "MyTable__c"
        },
        "schema": [],
        "linkedServiceName": {
            "referenceName": "<Salesforce Service Cloud linked service name>",
            "type": "LinkedServiceReference"
        }
    }
}
属性Property 说明Description 必须Required
typetype 数据集的 type 属性必须设置为 RelationalTableThe type property of the dataset must be set to RelationalTable. Yes
tableNametableName Salesforce Service Cloud 中的表名称。Name of the table in Salesforce Service Cloud. 否(如果指定了活动源中的“query”)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. 本部分提供 Salesforce Service Cloud 源和接收器支持的属性列表。This section provides a list of properties supported by Salesforce Service Cloud source and sink.

将 Salesforce Service Cloud 用作源类型Salesforce Service Cloud as a source type

从 Salesforce Service Cloud 复制数据时,复制活动的 source 节支持以下属性。To copy data from Salesforce Service Cloud, the following properties are supported in the copy activity source section.

属性Property 说明Description 必须Required
typetype 复制活动源的 type 属性必须设置为 SalesforceServiceCloudSourceThe type property of the copy activity source must be set to SalesforceServiceCloudSource. Yes
查询query 使用自定义查询读取数据。Use the custom query to read data. 可以使用 Salesforce 对象查询语言 (SOQL) 查询或 SQL-92 查询。You can use Salesforce Object Query Language (SOQL) query or SQL-92 query. 请在查询提示部分中查看更多提示。See more tips in query tips section. 如果未指定查询,将检索在数据集的“objectApiName”中指定的 Salesforce Service Cloud 对象的所有数据。If query is not specified, all the data of the Salesforce Service Cloud object specified in "objectApiName" in dataset will be retrieved. 否(如果指定了数据集中的“objectApiName”)No (if "objectApiName" in the dataset is specified)
readBehaviorreadBehavior 指示是查询现有记录,还是查询包括已删除记录在内的所有记录。Indicates whether to query the existing records, or query all records including the deleted ones. 如果未指定,默认行为是前者。If not specified, the default behavior is the former.
允许的值:query(默认值)、queryAllAllowed values: query (default), queryAll.
No

重要

任何自定义对象均需要 API 名称的“__c”部分。The "__c" part of API Name is needed for any custom object.

数据工厂 Salesforce 连接 API 名称列表

示例:Example:

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

将 Salesforce Service Cloud 用作接收器类型Salesforce Service Cloud as a sink type

向 Salesforce Service Cloud 复制数据时,复制活动的 sink 节支持以下属性。To copy data to Salesforce Service Cloud, the following properties are supported in the copy activity sink section.

属性Property 说明Description 必须Required
typetype 复制活动接收器的 type 属性必须设置为 SalesforceServiceCloudSinkThe type property of the copy activity sink must be set to SalesforceServiceCloudSink. Yes
writeBehaviorwriteBehavior 操作写入行为。The write behavior for the operation.
允许的值为 InsertUpsertAllowed values are Insert and Upsert.
否(默认值为 Insert)No (default is Insert)
externalIdFieldNameexternalIdFieldName 更新插入操作的外部的 ID 字段名称。The name of the external ID field for the upsert operation. 指定的字段必须在 Salesforce Service Cloud 对象中定义为“外部 ID 字段”。The specified field must be defined as "External ID Field" in the Salesforce Service Cloud object. 它相应的输入数据中不能有 NULL 值。It can't have NULL values in the corresponding input data. 对于“Upsert”是必需的Yes for "Upsert"
writeBatchSizewriteBatchSize 每批中写入到 Salesforce Service Cloud 的数据行计数。The row count of data written to Salesforce Service Cloud in each batch. 否(默认值为5,000)No (default is 5,000)
ignoreNullValuesignoreNullValues 指示是否忽略 NULL 值从输入数据期间写入操作。Indicates whether to ignore NULL values from input data during a write operation.
允许的值为 truefalseAllowed values are true and false.
- True:执行更新插入或更新操作时,保持目标对象中的数据不变。- 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:执行更新插入或更新操作时,将目标对象中的数据更新为 NULL。- False: Update the data in the destination object to NULL when you do an upsert or update operation. 执行插入操作时插入 NULL 值。Insert a NULL value when you do an insert operation.
否(默认值为 false)No (default is false)

示例:Example:

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

查询提示Query tips

从 Salesforce Service Cloud 报表检索数据Retrieve data from a Salesforce Service Cloud report

可通过将查询指定为 {call "<report name>"} 从 Salesforce Service Cloud 报表检索数据。You can retrieve data from Salesforce Service Cloud reports by specifying a query as {call "<report name>"}. 例如 "query": "{call \"TestReport\"}"An example is "query": "{call \"TestReport\"}".

从 Salesforce Service Cloud 回收站中检索删除的记录Retrieve deleted records from the Salesforce Service Cloud Recycle Bin

若要从 Salesforce Service Cloud 回收站中查询软删除的记录,需要将 readBehavior 指定为 queryAllTo query the soft deleted records from the Salesforce Service Cloud Recycle Bin, you can specify readBehavior as queryAll.

SOQL 与 SQL 查询语法之间的差异Difference between SOQL and SQL query syntax

从 Salesforce Service Cloud 中复制数据时,可以使用 SOQL 查询或 SQL 查询。When copying data from Salesforce Service Cloud, you can use either SOQL query or SQL query. 请注意,这两者具有不同的语法和功能支持,不要混用。Note that these two has different syntax and functionality support, do not mix it. 建议使用 Salesforce Service Cloud 原本就支持的 SOQL 查询。You are suggested to use the SOQL query, which is natively supported by Salesforce Service Cloud. 下表列出了主要差异:The following table lists the main differences:

语法Syntax SOQL 模式SOQL Mode SQL 模式SQL Mode
列选择Column selection 需要枚举要在查询中复制的字段,例如 SELECT field1, filed2 FROM objectnameNeed to enumerate the fields to be copied in the query, e.g. SELECT field1, filed2 FROM objectname 除了列选择之外,还支持 SELECT *SELECT * is supported in addition to column selection.
引号Quotation marks 字段/对象名称不能用引号引起来。Filed/object names cannot be quoted. 字段/对象名称可以用引号引起来,例如 SELECT "id" FROM "Account"Field/object names can be quoted, e.g. SELECT "id" FROM "Account"
日期时间格式Datetime format 请参考此处的详细信息和下一部分中的示例。Refer to details here and samples in next section. 请参考此处的详细信息和下一部分中的示例。Refer to details here and samples in next section.
布尔值Boolean values 表示为 FalseTrue,例如 SELECT … WHERE IsDeleted=TrueRepresented as False and True, e.g. SELECT … WHERE IsDeleted=True. 表示为 0 或 1,例如 SELECT … WHERE IsDeleted=1Represented as 0 or 1, e.g. SELECT … WHERE IsDeleted=1.
列重命名Column renaming 不支持。Not supported. 支持,例如:SELECT a AS b FROM …Supported, e.g.: SELECT a AS b FROM ….
关系Relationship 支持,例如 Account_vod__r.nvs_Country__cSupported, e.g. Account_vod__r.nvs_Country__c. 不支持。Not supported.

使用 DateTime 列上的 where 子句检索数据Retrieve data by using a where clause on the DateTime column

当指定 SOQL 或 SQL 查询时,请注意 DateTime 的格式差异。When you specify the SOQL or SQL query, pay attention to the DateTime format difference. 例如:For example:

  • SOQL 示例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')}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 示例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')}'}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')}'}

MALFORMED_QUERY:Truncated 错误Error of MALFORMED_QUERY: Truncated

如果遇到“MALFORMED_QUERY:Truncated”错误,通常是因为在数据中存在 JunctionIdList 类型列,而 Salesforce 在支持此类具有大量行的数据方面存在限制。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. 若要缓解这种情况,请尝试排除 JunctionIdList 列或限制要复制的行数(可以将其划分为多个复制活动运行)。To mitigate, try to exclude JunctionIdList column or limit the number of rows to copy (you can partition to multiple copy activity runs).

Salesforce Service Cloud 的数据类型映射Data type mapping for Salesforce Service Cloud

从 Salesforce Service Cloud 复制数据时,以下映射用于从 Salesforce Service Cloud 数据类型映射到数据工厂临时数据类型。When you copy data from Salesforce Service Cloud, the following mappings are used from Salesforce Service Cloud 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 Service Cloud 数据类型Salesforce Service Cloud data type 数据工厂临时数据类型Data Factory interim data type
自动编号Auto Number StringString
复选框Checkbox 布尔Boolean
货币Currency 小数Decimal
DateDate DateTimeDateTime
日期/时间Date/Time DateTimeDateTime
EmailEmail StringString
IDID StringString
查找关系Lookup Relationship StringString
多选择列表Multi-Select Picklist StringString
NumberNumber 小数Decimal
百分比Percent 小数Decimal
电话Phone StringString
选择列表Picklist StringString
文本Text StringString
文本区域Text Area StringString
文本区域(长型值)Text Area (Long) StringString
文本区域(丰富)Text Area (Rich) StringString
文本(加密)Text (Encrypted) StringString
URLURL StringString

查找活动属性Lookup activity properties

若要了解有关属性的详细信息,请查看 Lookup 活动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 in Data Factory, see Supported data stores.