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


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:



Before we perform steps listed in this article, you will need to make sure following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from 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).
  4. Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages

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:,, 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:



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:


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

  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 input

  2. In connections, we will create a ZS-HTTP connection to the 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:

    Final SSIS configuration

  6. Finally, run the package.


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