{"id":8806,"date":"2020-03-06T03:51:19","date_gmt":"2020-03-06T03:51:19","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=8806"},"modified":"2025-12-11T13:03:13","modified_gmt":"2025-12-11T13:03:13","slug":"make-web-api-destination-dynamic-url-body-headers","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/make-web-api-destination-dynamic-url-body-headers\/","title":{"rendered":"How to make Web API Destination dynamic (URL, Body, Headers)"},"content":{"rendered":"

Introduction<\/h2>\n

In this article, we will show how to work with the Web API Destination and\u00a0create a dynamic URL, Body or header. For this purpose, we will use the\u00a0following components:<\/p>\n

ZappySys SSIS PowerPack<\/a>\u00a0component to make things work:
\n

\n\n\n\n\n

\n\"SSIS<\/a><\/td>\n		SSIS Web API Destination (POST data to URL)<\/a><\/td>\n<\/tr>\n

\n\"JSON<\/a><\/td>\n		SSIS JSON Generator Transform<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

 <\/p>\n

Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
    \r\n \t
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n

    In these examples, we are going to show different examples of how to send\u00a0a dynamic URL, dynamic body and dynamic headers.<\/p>\n

    How to make Web API Destination dynamic URL with the OLEDB Source<\/h2>\n
      \n
    1. First of all, in this example, we are going to create a dynamic URL, we\u00a0will use need the following URLs:
      \nhttps:\/\/jsonplaceholder.typicode.com\/posts<\/a>,\u00a0      https:\/\/jsonplaceholder.typicode.com\/comments<\/a>,
      \n      https:\/\/jsonplaceholder.typicode.com\/todos<\/a>,\u00a0https:\/\/jsonplaceholder.typicode.com\/users<\/a>, and other similar URLs.<\/li>\n
    2. So, we will set the last part of the URL as a variable.<\/li>\n
    3. Also, will have a table with the values of the variable (posts, users,\u00a0comments, etc):\n
      CREATE TABLE [dbo].[webApiDest](\r\n\t[id] [smallint] NOT NULL,\r\n\t[type] [nchar](10) NULL,\r\n CONSTRAINT [PK_webApiDest] PRIMARY KEY CLUSTERED \r\n(\r\n\t[id] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\r\n) ON [PRIMARY]\r\nGO\r\nINSERT [dbo].[webApiDest] ([id], [type]) VALUES (1, N'posts     ')\r\nINSERT [dbo].[webApiDest] ([id], [type]) VALUES (2, N'comments  ')\r\nINSERT [dbo].[webApiDest] ([id], [type]) VALUES (3, N'todos     ')\r\nINSERT [dbo].[webApiDest] ([id], [type]) VALUES (4, N'albums    ')\r\nINSERT [dbo].[webApiDest] ([id], [type]) VALUES (5, N'photos    ')\r\nINSERT [dbo].[webApiDest] ([id], [type]) VALUES (6, N'users     ')\r\n<\/pre>\n
       <\/li>\n
    4. In addition, we are going to create a Data Flow with the following\u00a0components as follows:\"\"<\/a><\/li>\n
    5. Secondly, we will connect to the table created in the previous section
      \nusing the OLEDB Source component:<\/p>\n
      \"OLEDB-list-links\"<\/a>
      OLEDB-Source-Data<\/p><\/div><\/li>\n<\/ol>\n
      How to make Web API Destination dynamic URL in SSIS with the ZS JSON\u00a0Generator Transform<\/h2>\n
        \n
      1. Also, in the JSON Generator transform, right-click on Mapping and select\u00a0add elements:\n
      2. Check the add multiple bound options and check the two columns:\n
      3. Pay attention to this step because this and the next one are the most\u00a0important steps. Right-click the type element and select edit.\n
      4. The hide from output will hide the type from the output but will be\u00a0present as a variable. Also, when we want to hide in the JSON created that\u00a0element.\n
      5. In addition, the include columns in the downstream option are the option that\u00a0will allow sending the type as a variable from the JSON Generator Transform\u00a0to the Web API destination.<\/li>\n<\/ol>\n
        How to make Web API Destination dynamic URL in SSIS with the ZS JSON\u00a0Generator Transform<\/h2>\n
          \n
        1. First of all, we will add the input column for the body. This will send the\u00a0ZSON generator into the body.\n
        2. In connections, we will create a ZS-HTTP connection to the\u00a0https:\/\/jsonplaceholder.typicode.com\/<\/a> site:\n
        3. In the URL we will create a dynamic part with the type as a variable\u00a0that gets the values from the SQL Server table and the type column.<\/li>\n
        4. You can write <%type%> manually, or edit the URL to use placeholders and\u00a0get the type variable from the columns folder:\n
        5. The Web API configuration will be something like this:\n
        6. Finally, run the package.<\/li>\n<\/ol>\n
          Conclusion<\/h2>\n
          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:<\/p>\n