How to make Web API Destination dynamic (URL, Body, Headers)

Introduction

In this article, we will show how to work with the Web API Destination and create a dynamic URL, Body or header. For this purpose, we will use the following components:

ZappySys SSIS PowerPack component to make things work:

 

Prerequisites

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
  4. (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.

In these examples, we are going to show different examples of how to send a dynamic URL, dynamic body and dynamic headers.

How to make Web API Destination dynamic URL with the OLEDB Source

  1. First of all, in this example, we are going to create a dynamic URL, we will use need the following URLs:
    https://jsonplaceholder.typicode.com/posts
    https://jsonplaceholder.typicode.com/comments,
    https://jsonplaceholder.typicode.com/todoshttps://jsonplaceholder.typicode.com/users, and other similar URLs.
  2. So, we will set the last part of the URL as a variable.
  3. Also, will have a table with the values of the variable (posts, users, comments, etc):
     
  4. In addition, we are going to create a Data Flow with the following components as follows:
  5. Secondly, we will connect to the table created in the previous section
    using the OLEDB Source component:

    OLEDB-list-links

    OLEDB-Source-Data

How to make Web API Destination dynamic URL in SSIS with the ZS JSON Generator Transform

  1. Also, in the JSON Generator transform, right-click on Mapping and select add elements:
    convert-tsql-json

    add-elements-JSON

  2. Check the add multiple bound options and check the two columns:
    SSIS Multiple bound

    Add JSON elements

  3. Pay attention to this step because this and the next one are the most important steps. Right-click the type element and select edit.
    Edit-ssis-outstream

    Edit JSON element

  4. The hide from output will hide the type from the output but will be present as a variable. Also, when we want to hide in the JSON created that element.
    column-downstream

    Column downstream

  5. In addition, the include columns in the downstream option are the option that will allow sending the type as a variable from the JSON Generator Transform to the Web API destination.

How to make Web API Destination dynamic URL in SSIS with the ZS JSON Generator Transform

  1. First of all, we will add the input column for the body. This will send the ZSON generator into the body.
    Body-ssis-JSON

    Body input

  2. In connections, we will create a ZS-HTTP connection to the https://jsonplaceholder.typicode.com/ site:
    http connection

    SSIS HTTP Connection

  3. In the URL we will create a dynamic part with the type as a variable that gets the values from the SQL Server table and the type column.
  4. You can write <%type%> manually, or edit the URL to use placeholders and get the type variable from the columns folder:
    ssis dynamic link

    SSIS web api dynamic link

  5. The Web API configuration will be something like this:
    SSIS-configuration-web-api

    Final SSIS configuration

  6. Finally, run the package.

Conclusion

In this article, we learned how to send a dynamic URL to REST API. If you want to test the tool, you can download it here:

 

Posted in SSIS WEB API Destination and tagged , , , .