Wednesday, January 20, 2016

Using REST in SSIS

Today, I'm going to show you an easy way to use REST API calls to bring data into SSIS. Since I am doing this the easy way, I'm using Task Factory. That's right, 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. This blog post is not really covering all it's capabilities however. I want to focus on general usage and how to split values into separate columns when retrieving data in JSON format.

For this example, I am going to use the Citrix GoTo Webinar 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 GoTo Webinar 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.

REST Connection

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, you have to register on the developer version of the website.




Now, inside the the editor, you need an API Endpoint URL. In the case of GoTo Webinar, Citrix provides the base URL, and then the different parts of the URL for the 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 start time value, use the following token path.

times[0].startTime

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 end time 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. Check out the free trial of Task Factory to try it out for yourselves.

1 comment:

  1. Hey Shawn,

    Great article, very detailed and well written,using REST in SSIS. Example is also good.

    I have found another blog SSIS REST API to SQL which is also good .

    Check out this....

    ReplyDelete