Introduction
Please visit this page to see all Pre-Configured ready to use API connectors which you can use in SSIS API Source / SSIS API Destination OR API ODBC Driver (for non-SSIS Apps such as Excel, Power BI, Informatica).
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.
Connect to YouTube API in SSIS (OAuth Connection)
Very first step to call any Google API is to create an API project for Google Service and then enable APIs you like to call in Console. In our case we need to enable YouTube API.
Check this article to learn how to create API Project and obtain Client ID and Client Secret to connect to YouTube API. For ease of use use Default Application provided by ZappySys but we still recommend using your own Custom Application on OAuth Connection.
Before we try our first YouTube API example, we recommend you to get familiar with YouTube API here. Once you done with above steps you can perform the following steps to call YouTube API in SSIS.
- Open existing SSIS Project or Create new One in Visual Studio (i.e. SSDT)
- Create a new SSIS Package. Right click in the Connection Manager Panel and click “New Connection…”.
- When Dialog box opens select ZS-OAUTH connection type.
- Once OAuth UI opens, Configure Google connection like below.
- Select Service Type as Google
- You can select Default App for ease of use or Select Custom Application (Enter Client Id and Secret Obtained using these steps )
- In the Scope Enter below – One scope per line for calling correct YouTube API. Refer to YouTube API documentation incase you need extra permissions. For read only operation we just need below scopes. Last scope is only needed if you need to use YouTube Analytics Reports
123https://www.googleapis.com/auth/youtubehttps://www.googleapis.com/auth/youtube.readonlyhttps://www.googleapis.com/auth/yt-analytics.readonly
- Click Generate Token to obtain Refresh Token (You may see Login Prompt, and then Grant Permission Confirmation Screen)
- Click OK to save UI.
Call YouTube REST API Example
Now lets look at how to call YouTube API REST API Task this task is more suitable to call GET, POST, DELETE, PUT requests without parsing data in Rows and Columns. If you need to parse data and load into Database table then see next section (Use JSON Source)
- From SSIS Toolbox look for items starting with “ZS”. Drag and Drop [ZS Rest API Task] to Designer Surface.
- Double click it to configure like below.
- Select URL From Connection option
- Select OAuth connection created in previous section.
- Enter URL like below (For example get all playlists). Replace xxxxxxxxxxx with your own channel ID (Its usually found in URL when you visit Youtube Channel Homepage)
1https://www.googleapis.com/youtube/v3/playlists?part=snippet,id&channelId=xxxxxxxxxxxxx - Click Test Request Button
- Here is how it looks like
That’s it we have successfully configured Connection for YouTube API in SSIS. In the next section we will see how to use this connection and read various data from YouTube API.
Read YouTube Playlists in SSIS
Once we done creating OAuth Connection Manager we can move forward to read YouTube data inside Data Flow.
Configure SSIS JSON / REST API Source
- Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
- Double click on the Data Flow task to see Data Flow designer surface.
- From the SSIS toolbox drag and drop JSON Source on the dataflow designer surface.
- Double click JSON Source and enter URL as below (Use variable or Hardcode Channel Id). maxResults controls how many rows you want to get in each response. Rep
123https://www.googleapis.com/youtube/v3/playlists?part=snippet,id&maxResults=50&channelId={{User::varChannelId}}--OR--https://www.googleapis.com/youtube/v3/playlists?part=snippet,id&maxResults=50&channelId=xxxxxxxxxxxxxxxxxxxxxx - Check Use Credentials and select existing OAuth connection or Create New one
- Select Array Filter (Items node) or type $.items[*] as below.
- That’s it you can now Click Preview to see sample data. In the next section we will configure Pagination to read all records if you have more than 100 rows.
Configure YouTube REST API Pagination
Most REST APIs dont return data in one big response. So you have to make sure you implement looping / pagination to read more records. Luckily ZappySys supports many different Pagination Settings. Here is how to configure YouTube REST API Pagination.
- Go to Pagination Tab found on JSON Source and select following 3 settings
- Select Pagination Mode as Response Attribute Mode
- Enter Next Link as $.nextPageToken
- Enter Suffix for Next URL as &pageToken=<%nextlink%>
Read YouTube Playlist Videos in SSIS
So in previous section we saw simple API call to read all playlists from YouTube. Now lets see how to get information about Playlist Videos.
For that everything should remain same as previous section except change URL as below. Where xxxxxxxxxxxxxxxxxxxxx is your Playlist ID obtained from Previous Call.
1 |
https://www.googleapis.com/youtube/v3/playlistItems?part=snippet,id&maxResults=50&playlistId=xxxxxxxxxxxxxxxxxxxxx |
Get all Videos for YouTube Channel
If you want to get all video name and ID for specific channel then use below URL. Click here to read more. Replace xxxxxxxxxxxxxxxxxxx with your Channel ID.
1 |
https://www.googleapis.com/youtube/v3/search?channelId=xxxxxxxxxxxxxxxxxxx&part=snippet,id&order=date&maxResults=50 |
Read YouTube Video Views, Likes, Dislikes (Extended Videos)
Another common request from YouTube API is get Video Information such as Views, Likes, Dislikes, Comment Count etc. For that you can use this api . However the issue is this endpoint requires you to pass Comma separated Ids of Video. You can only pass 25 Ids in one requests so if you have 200 videos for which you need to get information then you have to make 8 requests (25 Ids in each Request). In below screenshot you can see we used Fiddler to debug web requests.
So here is how high level process to fetch extended information for all Videos in your channel. Full SSIS Package is attached here (SSIS 2012 Format).
- Get all videos for your channel by calling (Assuming you will paginate in below request.
1https://www.googleapis.com/youtube/v3/search?channelId=xxxxxxxxxxxxxxxxxxx&part=snippet,id&order=date&maxResults=50 - Build Video Id groups for 25 Ids from above result and submit requests. For example if above request returns 200 Videos then your request to fetch extended information may look like below.
12345https://www.googleapis.com/youtube/v3/videos?part=snippet,contentDetails,statistics&id=ID1,ID2,ID3........ID25https://www.googleapis.com/youtube/v3/videos?part=snippet,contentDetails,statistics&id=ID26,ID27,ID28........ID50................https://www.googleapis.com/youtube/v3/videos?part=snippet,contentDetails,statistics&id=ID176,ID177,ID178........ID200 - Parse response coming from each Request Above…. Each Response will give you details about 25 videos you requesting.
Here is the flow of our complete data flow to read extended information for all videos. You can read extended information one by one too but it wont be good idea if you have many videos.
We have used Following Components to achieve Bulk Mode
Read data from YouTube Analytics / Reporting API (Metrics, Dimensions Report)
So far we have seen basic YouTube APIs to extract your Channel information. However there is another very powerful API endpoint to query custom reports from YouTube. Check this YouTube Analytics API endpoint. In this section we will learn how to extract useful information from YouTube using Analytics API.
Step-By-Step
Let’s look at an example to read total views, estimated watch time, average view duration and some other metrics by date for channel you have access.
- Go to Data Flow, Drag ZS JSON Source from SSIS Toolbox
- Double click to configure.
- Check Use Credentials. Select OAuth connection we created in previous section.
- In the URL field, Enter Below Sample URL. Change Parameters as per your need or leave it default.
For example:
ids=channel==MINE (where MINE can be replaced by other channel id or keep it as MINE to get data for your own channel)
metrics (can be changed from any valid list here)
dimensions (can be changed to any valid list from here)
startDate (Must be YYYY-MM-DD format)
endDate (Must be YYYY-MM-DD format)
1https://youtubeanalytics.googleapis.com/v2/reports?ids=channel==MINE&metrics=views,estimatedMinutesWatched,averageViewDuration,averageViewPercentage,subscribersGained&dimensions=day&startDate=2019-03-01&endDate=2019-04-01 - Change Filter to $.rows[*]
- Go to 2D Array Transform tab.
Select Method as Simple 2-dimensional array EnterColumn Filter as $.columnHeaders[*].name - That’s it now Preview your data. See below over all steps.
Build YouTube Analytics / Reporting API Query (API Playground)
With Analytics API You can extract many interesting data. You can use Test Tool found on this page to build some interesting queries. Scroll to Common Use Cases section on that link. On the right side you can select Credentials Type as OAuth and click Execute.
https://developers.google.com/youtube/analytics/reference/reports/query
Loading YouTube API Data into SQL Server
- Inside Data Flow, Drag and drop Upsert Destination Component from SSIS Toolbox
- Connect our Source component to Upsert Destination
- Double click Upsert Destination to configure it
- Select Target Connection or click NEW to create new connection Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS
- Select Target Table or click NEW to create new table based on source columns
- Click on Mappings Tab to Auto map columns by name. You can change mappings as you need SSIS Upsert Destination - Columns Mappings
- Click OK to Save Upsert Destination Settings
- That's it, You are now ready to run data flow. NOTE: If you wish to debug data flow and see records when you run, add data viewer by right click on blue arrow > Click Enable Data Viewer
- To execute data flow, Right click anywhere inside Data Flow Surface and click Execute Task
Download Sample SSIS Package
SSIS Package is attached here (SSIS 2012 Format).
Conclusion
In this article we saw how to extract information from REST API such as YouTube REST API using OAuth 2.0. We also learned techniques like Pagination, JSON Parsing, Request Batching using multiple components to achieve full scale API integration. To explore many other scenarios not discussed in this article download SSIS PowerPack from here (includes 70+ Components).