Today, I'm going to show you an easy way to use REST API calls to bring data into SSIS. Task Factory has a new REST Source that enables users to retrieve data from websites using the REST API. It's a rather powerful source component that can be used in a number of ways. I want to focus on general usage and how to split values into separate columns when retrieving data in JSON format.
I am using the Citrix GoToWebinar API to retrieve data about past webinars. Since I can pull from any site that uses REST, I make it a point to use the GoToWebinar API reference. First of all, when using the REST Source, you have to create a connection manager. You will find that there are two different types. There is a generic REST connection and a REST OAuth2 connection.
In this example, I am using the OAuth2 connection. You will notice in the screenshot below that it requires an API key and an access token. Both of these are provided by the website that you are connecting to. Not to go too far into details there, but you have to register on the developer version of the website.
Now, inside the editor, you need an API Endpoint URL. In the case of GoToWebinar, Citrix provides the base URL, and then the different parts of the URL for the individual operations. What I really want to focus on here are the output columns. As you can see in the shot below, each column that needs to be returned will have to be manually added by using the 'Add New Output Column' button at the bottom of the window.
The column name can be whatever you like, of course, but you must also specify the Token Path. This information is provided on the Citrix website referenced above for the API. You can see that all I am doing here is returning information about past webinars. What I want to focus on here is the StartTime column.
On the left, when I click on Preview/Test API I have the ability to preview the data. Notice in the results grid the contents of the StartTime column. This column contains both start and end times of each webinar. Not something I want.
I need to get just the start time. Back to the API Endpoint tab. I have requested the results be returned in JSON. This will determine how I pull back just the start time from the times token path.
On the StartTime column, I need to change the Token Path. In order to return only the StartTime value, use the following token path.
It should look something like this.
Now when I preview the results, I get just the start time in my StartTime column.
If needed, I could also add a column for the EndTime and use the same JSON expression to retrieve the EndTime value.
As you can see, it is a very simple way to access data through REST API. Click here to watch a step-by-step video on how to use the REST Source with GoToWebinar.