Video tutorial

Watch this quick video to see the integration in action. It walks you through the end-to-end setup, including:

  • Installing the SSIS PowerPack
  • Configuring a secure connection to ServiceNow
  • Working with ServiceNow data directly inside SSIS
  • Exploring advanced API Source features
While this video uses the OData Connector as an example, the core concepts and setup process are exactly the same for the ServiceNow Connector.

Video doesn't load? Watch this video on YouTube.

Ready to dive in? Download the product to jump right in, or follow the step-by-step guide below to see how it works.

Prerequisites

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

  1. SQL Server Data Tools (SSDT) designer installed for Visual Studio.
  2. SQL Server Integration Services Projects 2022+ Visual Studio extension installed.
  3. SSIS PowerPack is installed.

Delete table row in SSIS

  1. Open Visual Studio and click Create a new project.

  2. Select Integration Services Project. Enter a name and location for your project, then click OK.

  3. From the SSIS Toolbox, drag and drop a Data Flow Task onto the Control Flow surface, and double-click it:

    Drag Data Flow Task onto Control Flow to use SSIS PowerPack Data Flow components

  4. Make sure you are in the Data Flow Task designer:

    Make sure you are in Data Flow designer in SSIS package

  5. 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.

  6. 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.
    SSIS API Destination (Predefined Templates) - Drag and Drop

  7. 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.
    API Destination - ServiceNow

  8. 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
    API Destination -

  9. 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:

    1. Go to https://developer.servicenow.com/dev.do and log in.
    2. Click on your profile picture.

    3. When a menu appears, click on Manage instance password or a similar option:

      Manage instance password in ServiceNow

    4. In a new window you will see your HTTP Basic Authentication credentials:

      Use instance credentials to authenticate to ServiceNow

    5. Done! Use this username and password when configuring HTTP Authentication parameters.

    API Connection Manager configuration

    Just perform these simple steps to finish authentication configuration:

    1. Set Authentication Type to Basic Auth (UserName and Password) [Http]
    2. Optional step. Modify API Base URL if needed (in most cases default will work).
    3. Fill in all the required parameters and set optional parameters if needed.
    4. 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
    ZappySys Http Connection
    Find full details in the ServiceNow Connector authentication reference.
    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:

    1. Login to your instance at https://your-instance-id.service-now.com.

    2. Start by clicking on menu item All, then search for oauth, and click Application Registry option:

      Locate OAuth configuration in ServiceNow

    3. Then click button New:

      Create new OAuth application in ServiceNow

    4. Continue by clicking Create an OAuth API endpoint for external clients option:

      Use ServiceNow OAuth application for external clients

    5. Move on by naming your OAuth application and entering a Client Secret (or let it be generated automatically).

    6. 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

    7. Finally give it a final touch by increasing the Refresh Token Lifespan value to 315,360,000 (it should last for 10 years):

      Configure OAuth application in ServiceNow
    8. 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.

    9. 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:

    1. Set Authentication Type to OAuth Authorization Code Grant (User Creds) [OAuth]
    2. Optional step. Modify API Base URL if needed (in most cases default will work).
    3. Fill in all the required parameters and set optional parameters if needed.
    4. Press Generate Token button to generate the tokens.
    5. 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...
    ZappySys OAuth Connection
    Find full details in the ServiceNow Connector authentication reference.
    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:

    1. Login to your instance at https://your-instance-id.service-now.com.

    2. Start by clicking on menu item All, then search for oauth, and click Application Registry option:

      Locate OAuth configuration in ServiceNow

    3. Then click button New:

      Create new OAuth application in ServiceNow

    4. Continue by clicking Create an OAuth API endpoint for external clients option:

      Use ServiceNow OAuth application for external clients

    5. Move on by naming your OAuth application and entering a Client Secret (or let it be generated automatically).

    6. 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

    7. Finally give it a final touch by increasing the Refresh Token Lifespan value to 315,360,000 (it should last for 10 years):

      Configure OAuth application in ServiceNow
    8. 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.

    9. 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:

    1. Set Authentication Type to OAuth Client Credentials Grant (Must be enabled by Admin) [OAuth]
    2. Optional step. Modify API Base URL if needed (in most cases default will work).
    3. Fill in all the required parameters and set optional parameters if needed.
    4. 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.
    ZappySys OAuth Connection
    Find full details in the ServiceNow Connector authentication reference.
    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:

    1. Login to your instance at https://your-instance-id.service-now.com.

    2. Start by clicking on menu item All, then search for oauth, and click Application Registry option:

      Locate OAuth configuration in ServiceNow

    3. Then click button New:

      Create new OAuth application in ServiceNow

    4. Continue by clicking Create an OAuth API endpoint for external clients option:

      Use ServiceNow OAuth application for external clients

    5. Move on by naming your OAuth application and entering a Client Secret (or let it be generated automatically).

    6. 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

    7. Finally give it a final touch by increasing the Refresh Token Lifespan value to 315,360,000 (it should last for 10 years):

      Configure OAuth application in ServiceNow
    8. 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.

    9. 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:

    1. Set Authentication Type to OAuth Password Grant [OAuth]
    2. Optional step. Modify API Base URL if needed (in most cases default will work).
    3. Fill in all the required parameters and set optional parameters if needed.
    4. 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...
    ZappySys OAuth Connection
    Find full details in the ServiceNow Connector authentication reference.

  10. Select [Dynamic Table] table from the dropdown, then select Delete 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
    [Dynamic Table]
    Delete
    Required Parameters
    TableName Fill-in the parameter...
    SysId Fill-in the parameter...
    Optional Parameters
    RawOutputDataRowTemplate {}
    EnableRawOutputModeSingleRow True
    ContineOn404Error True
    SSIS API Destination - Access table operation

  11. 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.
    API Destination - ServiceNow

  12. That's it; we successfully configured the POST API Call. In a few clicks we configured the ServiceNow API call using ZappySys ServiceNow Connector

    Execute Package

ServiceNow Connector actions

Need another use case? Pick the next ServiceNow action in SSIS below.

Conclusion

You now know how to delete table row in SSIS without writing complex code. ServiceNow Connector handled pagination and authentication automatically.

Ready to get started? Download the trial or ping us via chat if you need help:

Download SSIS PowerPack
Chat with an Expert