ServiceNow Connector for Azure Data Factory (SSIS) How to Make Generic API Request (Bulk Write)

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 API Request (Bulk Write) 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.
Read the data from the source, using any desired source component. You can even make an API call using the ZappySys JSON/XML/API Source and read data from there. In this example, we will use an OLE DB Source component to read real-time data from a SQL Server database.
From the SSIS Toolbox drag and drop API Destination (Predefined Templates) on the Data Flow Designer surface and connect source component with it, and double click to edit it.
Select New Connection to create a new connection:

API Destination - ServiceNow

Read and write ServiceNow data effortlessly. Integrate, manage, and automate incidents, tasks, attachments, and records — almost no coding required.
To configure the ServiceNow connector, choose one of the following methods:
- Choose from Popular Connector List: Select a pre-installed service directly from the dropdown menu.
- Search Online: Use this to find and download a new connector file to your computer.
- Use Saved/Downloaded File: Once the file is downloaded, browse your local drive to load it into the configuration.
After that, just click Continue >>:

ServiceNow

Proceed with selecting the desired Authentication Type. Then select API Base URL (in most cases default one is the right one). Finally, fill in all the required parameters and set optional parameters if needed. You may press a link Steps to Configure which will help set certain parameters. More info is available in Authentication section.

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 / Write) table from the dropdown, then select Insert, Update as operation, and hit Preview Data:

API Destination - 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 / Write)

Insert, Update

Required Parameters
Url	Fill-in the parameter...
Request Method	Fill-in the parameter...
Optional Parameters
IsMultiPart
Filter
Request Format (Content-Type)	Default
Body	{$rows$}
JsonOutputFormat	Multicontent
DoNotOutputNullProperty
Batch Size (Default=1)	1
Meta Detection Order	StaticDynamicVirtual
Input Columns - For Mapping (e.g. MyCol1:string(10); MyCol2:int32 ...) - Use bool, int32, int64, datetime, decimal, double
Output Columns (e.g. MyCol1:string(10); MyCol2:int32 ...) - Use bool, int32, int64, datetime, decimal, double
Request Format
Response Format	Default
Headers	Accept: / \|\| Cache-Control: no-cache
Csv - Column Delimiter	,
Csv - Row Delimiter	{NEWLINE}
Csv - Quote Around Value	True
Csv - Always Quote regardless type
Encoding
CharacterSet
Writer DateTime Format
Csv - Has Header Row	True
Xml - ElementsToTreatAsArray
Layout Map	<?xml version="1.0" encoding="utf-8"?> <!-- Example#1: Output all columns --> <settings> <dataset id="root" main="True" readfrominput="True" /> <map src="*" /> </settings> <!-- Example#2: Records under array <?xml version="1.0" encoding="utf-8"?> <settings singledataset="True"> <dataset id="root" main="True" readfrominput="True" /> <map name="MyArray" dataset="root" maptype="DocArray"> <map src="OrderID" name="OrderID" /> <map src="OrderDate" name="OrderDate" /> </map> </settings> --> <!-- Example#3: Records under nested section <?xml version="1.0" encoding="utf-8"?> <settings> <dataset id="dsRoot" main="True" readfrominput="True" /> <map name="NestedSection"> <map src="OrderID" name="OrderID_MyLabel" /> <map src="OrderDate" name="OrderDate_MyLabel" /> </map> </settings> -->

SSIS API Destination - Access table operation

Finally, map the desired columns:

API Destination - ServiceNow

Read and write ServiceNow data effortlessly. Integrate, manage, and automate incidents, tasks, attachments, and records — almost no coding required.
That's it; we successfully configured the POST API Call. In a few clicks we configured the ServiceNow API call using ZappySys ServiceNow Connector

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.

Download ServiceNow Connector for Azure Data Factory (SSIS) Documentation