Contents
hide
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:- SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
- (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
- 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/todos, https://jsonplaceholder.typicode.com/users, and other similar URLs. - So, we will set the last part of the URL as a variable.
- Also, will have a table with the values of the variable (posts, users, comments, etc):
123456789101112131415CREATE TABLE [dbo].[webApiDest]([id] [smallint] NOT NULL,[type] [nchar](10) NULL,CONSTRAINT [PK_webApiDest] PRIMARY KEY CLUSTERED([id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOINSERT [dbo].[webApiDest] ([id], [type]) VALUES (1, N'posts ')INSERT [dbo].[webApiDest] ([id], [type]) VALUES (2, N'comments ')INSERT [dbo].[webApiDest] ([id], [type]) VALUES (3, N'todos ')INSERT [dbo].[webApiDest] ([id], [type]) VALUES (4, N'albums ')INSERT [dbo].[webApiDest] ([id], [type]) VALUES (5, N'photos ')INSERT [dbo].[webApiDest] ([id], [type]) VALUES (6, N'users ') - In addition, we are going to create a Data Flow with the following components as follows:
- Secondly, we will connect to the table created in the previous section
using the OLEDB Source component:
OLEDB-Source-Data
How to make Web API Destination dynamic URL in SSIS with the ZS JSON Generator Transform
- Also, in the JSON Generator transform, right-click on Mapping and select add elements:
add-elements-JSON
- Check the add multiple bound options and check the two columns:
Add JSON elements
- 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
- 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
- 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
- First of all, we will add the input column for the body. This will send the ZSON generator into the body.
Body input
- In connections, we will create a ZS-HTTP connection to the https://jsonplaceholder.typicode.com/ site:
SSIS HTTP Connection
- 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.
- You can write <%type%> manually, or edit the URL to use placeholders and get the type variable from the columns folder:
SSIS web api dynamic link
- The Web API configuration will be something like this:
Final SSIS configuration
- 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:
