Edit

Share via

Copy data from ServiceNow using Azure Data Factory or Synapse Analytics

  • Article

APPLIES TO: Azure Data Factory Azure Synapse Analytics

Tip

Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!

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

Important

The new ServiceNow connector provides improved native ServiceNow support. If you are using the legacy ServiceNow connector in your solution, you are recommended to upgrade your ServiceNow connector at your earliest convenience. Refer to this section for details on the difference between the legacy and latest version.

Supported capabilities

This ServiceNow connector is supported for the following capabilities:

Supported capabilities IR
Copy activity (source/-) ① ②
Lookup activity ① ②

① Azure integration runtime ② Self-hosted integration runtime

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

The service provides a built-in driver to enable connectivity. Therefore you don't need to manually install any driver using this connector.

Please use the actual value instead of the displayed value in ServiceNow.

Prerequisite

To use this connector, you need to have a role with at least read access to sys_db_object and sys_dictionary tables in ServiceNow.

Getting started

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

Create a linked service to ServiceNow using UI

Use the following steps to create a linked service to ServiceNow 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:

  2. Search for ServiceNow and select the ServiceNow connector.

    Screenshot of the ServiceNow connector.

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

    Screenshot of linked service configuration for ServiceNow.

Connector configuration details

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

Linked service properties

The following properties are supported for ServiceNow linked service:

Property Description Required
type The type property must be set to: ServiceNowV2 Yes
endpoint The endpoint of the ServiceNow server (http://<instance>.service-now.com). Yes
authenticationType The authentication type to use.
Allowed values are: Basic, OAuth2		 Yes
username The user name used to connect to the ServiceNow server for Basic and OAuth2 authentication. Yes
password The password corresponding to the user name for Basic and OAuth2 authentication. Mark this field as a SecureString to store it securely, or reference a secret stored in Azure Key Vault. Yes
clientId The client ID for OAuth2 authentication. Yes for OAuth authentication
clientSecret The client secret for OAuth2 authentication. Mark this field as a SecureString to store it securely, or reference a secret stored in Azure Key Vault. Yes for OAuth authentication
grantType Specifies the type of OAuth2.0 flow that the client app uses to access token. The default value is password. Yes for OAuth authentication

Example:

{
    "name": "ServiceNowLinkedService",
    "properties": {
        "type": "ServiceNowV2",
        "typeProperties": {
            "endpoint" : "http://<instance>.service-now.com",
            "authenticationType" : "Basic",
            "username" : "<username>",
            "password": {
                 "type": "SecureString",
                 "value": "<password>"
            }
        }
    }
}

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 ServiceNow dataset.

To copy data from ServiceNow, set the type property of the dataset to ServiceNowV2Object. The following properties are supported:

Property Description Required
type The type property of the dataset must be set to: ServiceNowV2Object Yes
tableName Name of the table. Yes

Example

{
    "name": "ServiceNowDataset",
    "properties": {
        "type": "ServiceNowV2Object",
        "typeProperties": {
            "tableName": "<table name>"
        },
        "schema": [],
        "linkedServiceName": {
            "referenceName": "<ServiceNow 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 ServiceNow source.

ServiceNow as source

To copy data from ServiceNow, set the source type in the copy activity to ServiceNowV2Source. 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: ServiceNowV2Source Yes
expression Use the expression to read data. You can configure the expression in Query builder. It has the same usage as the condition builder in ServiceNow. For instructions on how to use it, see this article. You can also use expression parameters. No
Under expression
type The expression type. Values can be Constant (default), Unary, Binary, Field and Nary. No
value The constant value. Yes when the expression type is Constant or Field
operators The operator value. For more information about operators, see Operators available for choice fields containing strings section in this article. Yes when the expression type is Unary or Binary
operands List of expressions on which operator is applied. Yes when the expression type is Unary or Binary

Example:

"activities": [
    {
        "name": "CopyFromServiceNow",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<ServiceNow input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "ServiceNowV2Source",
                "expression": {
                    "type": "Nary",
                    "operators": [
                        "<"
                    ],
                    "operands": [
                        {
                            "type": "Field",
                            "value": "u_founded"
                        },
                        {
                            "type": "Constant",
                            "value": "2000"
                        }
                    ]
                }
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Using expression parameters

You can configure the expression parameter in Query builder by selecting Add dynamic content. The parameter type should be Object, and the value should follow the format shown in the example JSON below:

 {
	"type": "Nary",
	"operators": [
		"="
	],
	"operands": [
		{
			"type": "Field",
			"value": "col"
		},
		{
			"type": "Constant",
			"value": "val"
		}
	]
}

Here is an example of the source JSON using the expression parameter:

"activities": [
    {
        "name": "CopyFromServiceNow",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<ServiceNow input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "ServiceNowV2Source",
                "expression": {
                    "type": "Expression",
                    "value": "@pipeline().parameters.expressionParameter"
                }
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Lookup activity properties

To learn details about the properties, check Lookup activity.

Upgrade the ServiceNow connector

Here are the steps that help you to upgrade your ServiceNow connector:

  1. Create a new linked service by referring to Linked service properties.
  2. Query in source is upgraded to Query builder, which has the same usage as the condition builder in ServiceNow. Learn how to configure it referring to ServiceNow as source.

Differences between ServiceNow and ServiceNow (legacy)

The ServiceNow connector offers new functionalities and is compatible with most features of ServiceNow (legacy) connector. The table below shows the feature differences between ServiceNow and ServiceNow (legacy).

ServiceNow  ServiceNow (legacy)
useEncryptedEndpoints, useHostVerification and usePeerVerification are not supported in the linked service. Support useEncryptedEndpoints, useHostVerification and usePeerVerification in the linked service.
Support Query builder in the source. Query builder is not supported in the source.
SQL-based queries are not supported. Support SQL-based queries.
sortBy queries are not supported in Query builder. Support sortBy queries in Query.
You can view the schema in the dataset. You can't view the schema in the dataset.

Related content

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