ServiceNow Connector for Azure Data Factory (SSIS) : Make generic REST API request

Integrate Azure Data Factory (SSIS) and ServiceNow

Learn how to make generic REST API request using the ServiceNow Connector for Azure Data Factory (SSIS). This connector enables you to read and write ServiceNow data effortlessly. Integrate, manage, and automate incidents, tasks, attachments, and records — almost no coding required. We'll walk you through the exact setup.

Let's dive in!

ServiceNow Connector

Powering this integration

High performance
Bidirectional
Low-code / No-code
Easy to use

Built on the ZappySys API Connector Framework, the ServiceNow Connector for SSIS is a high-performance, low-code / no-code solution. It enables you to read and write ServiceNow data without any programming right in your SSIS package. Part of the SSIS PowerPack.

Documentation

Prerequisites

Before we begin, make sure the following prerequisites are met:

SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft).
Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
SSIS PowerPack is installed (if you are new to SSIS PowerPack, then get started!).

Make generic REST API request in SSIS

Begin with opening Visual Studio and Create a New Project.
Select Integration Service Project and in new project window set the appropriate name and location for project. And click OK.

In the new SSIS project screen you will find the following:
- SSIS ToolBox on left side bar
- Solution Explorer and Property Window on right bar
- Control flow, data flow, event Handlers, Package Explorer in tab windows
- Connection Manager Window in the bottom
Note: If you don't see ZappySys SSIS PowerPack Task or Components in SSIS Toolbox, please refer to this help link.
Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox. Double click on the Data Flow Task to see Data Flow designer.
From the SSIS toolbox drag and API Source (Predefined Templates) on the data flow designer surface, and double click on it to edit it:
Select New Connection to create a new connection:
Use a preinstalled ServiceNow Connector from Popular Connector List or press Search Online radio button to download ServiceNow Connector. Once downloaded simply use it in the configuration:

ServiceNow

Now it's time to configure authentication. Firstly, configure authentication settings in ServiceNow service and then proceed by configuring API Connection Manager. Start by expanding an authentication type:

Basic Auth (UserName and Password)

ServiceNow authentication

Authenticate using ServiceNow instance username and password.

Locate your ServiceNow instance credentials and use them for HTTP authentication:

Go to https://developer.servicenow.com/dev.do and log in.
Click on your profile picture.
When a menu appears, click on Manage instance password or a similar option:
In a new window you will see your HTTP Basic Authentication credentials:
Done! Use this username and password when configuring HTTP Authentication parameters.

API Connection Manager configuration

Just perform these simple steps to finish authentication configuration:

Set Authentication Type to Basic Auth (UserName and Password) [Http]
Optional step. Modify API Base URL if needed (in most cases default will work).
Fill in all the required parameters and set optional parameters if needed.
Finally, hit OK button:

ServiceNow

Basic Auth (UserName and Password) [Http]

https://[$InstanceName$].service-now.com/api/now

Required Parameters
InstanceName (e.g. mycompany)	Fill-in the parameter...
User name	Fill-in the parameter...
Password	Fill-in the parameter...
Optional Parameters
ApiVersion	v2
RetryMode	RetryWhenStatusCodeMatch
RetryStatusCodeList	429
RetryCountMax	5
RetryMultiplyWaitTime	True
Date Time Value Handling

OAuth Authorization Code Grant (User Creds)

ServiceNow authentication

Authenticate using OAuth 2.0 Authorization Code Flow (3-legged)

Create OAuth application and use it for authentication:

Login to your instance at https://your-instance-id.service-now.com.
Start by clicking on menu item All, then search for oauth, and click Application Registry option:
Then click button New:
Continue by clicking Create an OAuth API endpoint for external clients option:
Move on by naming your OAuth application and entering a Client Secret (or let it be generated automatically).
Click on the lock icon next to the Redirect URL. Enter https://zappysys.com/oauth. This is not needed if you use Client Credentials Grant or Password Grant
Finally give it a final touch by increasing the Refresh Token Lifespan value to 315,360,000 (it should last for 10 years):
Change Default Grant Type as per your need.
NOTE: If you prefer to use Client Credentials grant (Service account style access without using real userid-password) then your admin must enable it. Client Credentials is only supported on instances where it is explicitly enabled by the ServiceNow administrator. Its not supported in Dev / Free instance.
Done! Now you can use Client ID and Client Secret when configuring OAuth Authentication parameters.

Enable Client Credentials Grant Type

Enable the Client Credentials Grant Type: Go to System Properties > All Properties, create a new property named glide.oauth.inbound.client.credential.grant_type.enabled, and set it to true.
Configure the OAuth Application: Navigate to System OAuth > Application Registry, click New, and select Create an OAuth API endpoint for external clients. Set the name and save the record. In the Default Grant Type field, select Client Credentials and associate it with the integration user.

API Connection Manager configuration

Just perform these simple steps to finish authentication configuration:

Set Authentication Type to OAuth Authorization Code Grant (User Creds) [OAuth]
Optional step. Modify API Base URL if needed (in most cases default will work).
Fill in all the required parameters and set optional parameters if needed.
Press Generate Token button to generate the tokens.
Finally, hit OK button:

ServiceNow

OAuth Authorization Code Grant (User Creds) [OAuth]

https://[$InstanceName$].service-now.com/api/now

Required Parameters
Redirect URI (*Must match as App Registry Page*)	Fill-in the parameter...

OAuth Client Credentials Grant (Must be enabled by Admin)

ServiceNow authentication

Authenticate using OAuth Client Credentials Grant (Client ID and Client Secret). [API reference]

Create OAuth application and use it for authentication:

Login to your instance at https://your-instance-id.service-now.com.
Start by clicking on menu item All, then search for oauth, and click Application Registry option:
Then click button New:
Continue by clicking Create an OAuth API endpoint for external clients option:
Move on by naming your OAuth application and entering a Client Secret (or let it be generated automatically).
Click on the lock icon next to the Redirect URL. Enter https://zappysys.com/oauth. This is not needed if you use Client Credentials Grant or Password Grant
Finally give it a final touch by increasing the Refresh Token Lifespan value to 315,360,000 (it should last for 10 years):
Change Default Grant Type as per your need.
NOTE: If you prefer to use Client Credentials grant (Service account style access without using real userid-password) then your admin must enable it. Client Credentials is only supported on instances where it is explicitly enabled by the ServiceNow administrator. Its not supported in Dev / Free instance.
Done! Now you can use Client ID and Client Secret when configuring OAuth Authentication parameters.

Enable Client Credentials Grant Type

Enable the Client Credentials Grant Type: Go to System Properties > All Properties, create a new property named glide.oauth.inbound.client.credential.grant_type.enabled, and set it to true.
Configure the OAuth Application: Navigate to System OAuth > Application Registry, click New, and select Create an OAuth API endpoint for external clients. Set the name and save the record. In the Default Grant Type field, select Client Credentials and associate it with the integration user.

API Connection Manager configuration

Just perform these simple steps to finish authentication configuration:

Set Authentication Type to OAuth Client Credentials Grant (Must be enabled by Admin) [OAuth]
Optional step. Modify API Base URL if needed (in most cases default will work).
Fill in all the required parameters and set optional parameters if needed.
Finally, hit OK button:

ServiceNow

OAuth Client Credentials Grant (Must be enabled by Admin) [OAuth]

https://[$InstanceName$].service-now.com/api/now

There are no parameters to configure.

OAuth Password Grant

ServiceNow authentication

Authenticate using OAuth Password Grant (ServiceNow instance username, password, OAuth Client ID, and Client Secret). [API reference]

Create OAuth application and use it for authentication:

Login to your instance at https://your-instance-id.service-now.com.
Start by clicking on menu item All, then search for oauth, and click Application Registry option:
Then click button New:
Continue by clicking Create an OAuth API endpoint for external clients option:
Move on by naming your OAuth application and entering a Client Secret (or let it be generated automatically).
Click on the lock icon next to the Redirect URL. Enter https://zappysys.com/oauth. This is not needed if you use Client Credentials Grant or Password Grant
Finally give it a final touch by increasing the Refresh Token Lifespan value to 315,360,000 (it should last for 10 years):
Change Default Grant Type as per your need.
NOTE: If you prefer to use Client Credentials grant (Service account style access without using real userid-password) then your admin must enable it. Client Credentials is only supported on instances where it is explicitly enabled by the ServiceNow administrator. Its not supported in Dev / Free instance.
Done! Now you can use Client ID and Client Secret when configuring OAuth Authentication parameters.

Enable Client Credentials Grant Type

Enable the Client Credentials Grant Type: Go to System Properties > All Properties, create a new property named glide.oauth.inbound.client.credential.grant_type.enabled, and set it to true.
Configure the OAuth Application: Navigate to System OAuth > Application Registry, click New, and select Create an OAuth API endpoint for external clients. Set the name and save the record. In the Default Grant Type field, select Client Credentials and associate it with the integration user.

API Connection Manager configuration

Just perform these simple steps to finish authentication configuration:

Set Authentication Type to OAuth Password Grant [OAuth]
Optional step. Modify API Base URL if needed (in most cases default will work).
Fill in all the required parameters and set optional parameters if needed.
Finally, hit OK button:

ServiceNow

OAuth Password Grant [OAuth]

https://[$InstanceName$].service-now.com/api/now

Required Parameters
User name	Fill-in the parameter...
Password	Fill-in the parameter...

Select Generic Table (Bulk Read or Write) table from the dropdown and hit Preview Data:

API Source - ServiceNow

Read and write ServiceNow data effortlessly. Integrate, manage, and automate incidents, tasks, attachments, and records — almost no coding required.

ServiceNow

Generic Table (Bulk Read or Write)

Required Parameters
HTTP - Url or File Path	Fill-in the parameter...
HTTP - Request Method	Fill-in the parameter...
Optional Parameters
HTTP - Request Body
HTTP - Is MultiPart Body (Pass File data/Mixed Key/value)
HTTP - Request Format (Content-Type)	ApplicationJson
HTTP - Headers (e.g. hdr1:aaa \|\| hdr2:bbb)	Accept: / \|\| Cache-Control: no-cache
Parser - Response Format (Default=Json)	Default
Parser - Filter (e.g. $.rows[*] )
Parser - Encoding
Parser - CharacterSet
Download - Enable reading binary data	False
Download - File overwrite mode	AlwaysOverwrite
Download - Save file path
Download - Enable raw output mode as single row	False
Download - Raw output data RowTemplate	{Status:'Downloaded'}
Download - Request Timeout (Milliseconds)
General - Enable Custom Search/Replace
General - SearchFor (e.g. (\d)-(\d)--regex)
General - ReplaceWith (e.g. $1-***)
General - File Compression Type
General - Date Format
General - Enable Big Number Handling	False
General - Wait time (Ms) - Helps to slow down pagination (Use for throttling)	0
JSON/XML - ExcludedProperties (e.g. meta,info)
JSON/XML - Flatten Small Array (Not preferred for more than 10 items)
JSON/XML - Max Array Items To Flatten	10
JSON/XML - Array Transform Type
JSON/XML - Array Transform Column Name Filter
JSON/XML - Array Transform Row Value Filter
JSON/XML - Array Transform Enable Custom Columns
JSON/XML - Enable Pivot Transform
JSON/XML - Array Transform Custom Columns
JSON/XML - Pivot Path Replace With
JSON/XML - Enable Pivot Path Search Replace	False
JSON/XML - Pivot Path Search For
JSON/XML - Include Pivot Path	False
JSON/XML - Throw Error When No Match for Filter	False
JSON/XML - Parent Column Prefix
JSON/XML - Include Parent When Child Null	False
Pagination - Mode
Pagination - Attribute Name (e.g. page)
Pagination - Increment By (e.g. 100)	1
Pagination - Expression for Next URL (e.g. $.nextUrl)
Pagination - Wait time after each request (milliseconds)	0
Pagination - Max Rows Expr
Pagination - Max Pages Expr
Pagination - Max Rows DataPath Expr
Pagination - Max Pages	0
Pagination - End Rules
Pagination - Next URL Suffix
Pagination - Next URL End Indicator
Pagination - Stop Indicator Expr
Pagination - Current Page
Pagination - End Strategy Type	DetectBasedOnRecordCount
Pagination - Stop based on this Response StatusCode
Pagination - When EndStrategy Condition Equals	True
Pagination - Max Response Bytes	0
Pagination - Min Response Bytes	0
Pagination - Error String Match
Pagination - Enable Page Token in Body	False
Pagination - Placeholders (e.g. {page})
Pagination - Has Different NextPage Info	False
Pagination - First Page Body Part
Pagination - Next Page Body Part
Csv - Column Delimiter	,
Csv - Has Header Row	True
Csv - Throw error when column count mismatch	False
Csv - Throw error when no record found	False
Csv - Allow comments (i.e. line starts with # treat as comment and skip line)	False
Csv - Comment Character	#
Csv - Skip rows	0
Csv - Ignore Blank Lines	True
Csv - Skip Empty Records	False
Csv - Skip Header Comment Rows	0
Csv - Trim Headers	False
Csv - Trim Fields	False
Csv - Ignore Quotes	False
Csv - Treat Any Blank Value As Null	False
Xml - ElementsToTreatAsArray

SSIS API Source - Read from table or endpoint

That's it! We are done! Just in a few clicks we configured the call to ServiceNow using ServiceNow Connector.

You can load the source data into your desired destination using the Upsert Destination , which supports SQL Server, PostgreSQL, and Amazon Redshift. We also offer other destinations such as CSV , Excel , Azure Table , Salesforce , and more . You can check out our SSIS PowerPack Tasks and components for more options. (*loaded in Trash Destination)

Deploy SSIS package to Azure Data Factory (ADF)

Once your SSIS package is complete, deploy it to the Azure-SSIS runtime within Azure Data Factory. The setup process requires you to upload the SSIS PowerPack installer to Azure Blob Storage and then customize the runtime configuration using the main.cmd file. For a complete walkthrough of these steps, see our detailed guide on the Azure Data Factory (SSIS) and ServiceNow integration.

Conclusion

And there you have it — a complete guide on how to make generic REST API request in Azure Data Factory (SSIS) without writing complex code. All of this was powered by ServiceNow Connector, which handled the REST API pagination and authentication for us automatically.

Download the trial now or ping us via chat if you have any questions or are looking for a specific feature (you can also reach out to us by submitting a ticket):

Download Chat

More actions supported by ServiceNow Connector

Got another use case in mind? We've documented the exact setups for a variety of essential ServiceNow operations directly in Azure Data Factory (SSIS), so you can skip the trial and error. Find your next step-by-step guide below: