{"id":6894,"date":"2019-05-06T18:06:07","date_gmt":"2019-05-06T18:06:07","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=6894"},"modified":"2025-03-19T13:08:56","modified_gmt":"2025-03-19T13:08:56","slug":"read-youtube-api-data-ssis-videos-channels-playlists","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-youtube-api-data-ssis-videos-channels-playlists\/","title":{"rendered":"How to read YouTube API data in SSIS (Videos, Channels, Playlists)"},"content":{"rendered":"

Introduction<\/h2>\n
UPDATE:<\/strong>\u00a0ZappySys has released a brand new API Connector for YouTube<\/a> which makes it much simpler to\u00a0Read\/Write YouTube Data in SSIS<\/strong> compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid learning curve with API then use newer approach.<\/p>\n

Please visit this page to see all<\/a>\u00a0Pre-Configured ready to use API connectors which you can use in SSIS API Source<\/a> \/ SSIS API Destination<\/a> OR\u00a0API ODBC Driver<\/a> (for non-SSIS Apps such as Excel, Power BI, Informatica).<\/p>\n<\/div><\/div>\n\"\"<\/a>In last few articles<\/a> we saw how to read data from various Google Services. In this article we will see how to read YouTube API data in SSIS. This blog mainly focus on SSIS approach but steps mentioned to call Google APIs can be useful for any developer regardless which programming language or toolset you use.<\/p>\n

 <\/p>\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

    Connect to YouTube API in SSIS (OAuth Connection)<\/h2>\n

    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.<\/p>\n

    Check this article<\/a>\u00a0to 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\u00a0 on OAuth Connection.<\/p>\n

    Before we try our first YouTube API example, we recommend you to get familiar with YouTube API here<\/a>.\u00a0Once you done with above steps you can perform the following steps to call YouTube API in SSIS.<\/p>\n

      \n
    1. Open existing SSIS Project or Create new One in Visual Studio (i.e. SSDT)<\/li>\n
    2. Create a new SSIS Package. Right click in the Connection Manager Panel and click “New Connection…”<\/strong>.
      \n\"SSIS<\/li>\n
    3. When Dialog box opens select ZS-OAUTH<\/strong> connection type.\n
    4. Once OAuth UI opens, Configure Google connection like below.\n
        \n
      1. Select Service Type as Google<\/strong><\/li>\n
      2. You can select Default App for ease of use or Select Custom Application (Enter Client Id and Secret Obtained using these steps<\/a> )<\/li>\n
      3. In the Scope Enter below – One scope per line for calling correct YouTube API. Refer to YouTube API documentation<\/a> 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<\/a>\n
        https:\/\/www.googleapis.com\/auth\/youtube\r\nhttps:\/\/www.googleapis.com\/auth\/youtube.readonly\r\nhttps:\/\/www.googleapis.com\/auth\/yt-analytics.readonly\r\n<\/pre>\n<\/li>\n<\/ol>\n<\/li>\n
      4. Click Generate Token to obtain Refresh Token (You may see Login Prompt, and then Grant Permission Confirmation Screen)\n
      5. Click OK to save UI.<\/li>\n<\/ol>\n
        Call YouTube REST API Example<\/h2>\n
        Now lets look at how to call YouTube API REST API Task<\/a>\u00a0this 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)<\/p>\n
          \n
        1. From SSIS Toolbox look for items starting with \u201cZS\u201d. Drag and Drop\u00a0[ZS Rest API Task]<\/b>\u00a0to Designer Surface.
          \n\"SSIS<\/li>\n
        2. Double click it to configure like below.\n
            \n
          1. Select URL From Connection option<\/li>\n
          2. Select OAuth connection created in previous section.<\/li>\n
          3. Enter URL like below (For example get all playlists<\/a>). Replace xxxxxxxxxxx with your own channel ID (Its usually found in URL when you visit Youtube Channel Homepage)\n
            https:\/\/www.googleapis.com\/youtube\/v3\/playlists?part=snippet,id&channelId=xxxxxxxxxxxxx<\/pre>\n<\/li>\n
          4. Click Test Request Button<\/li>\n<\/ol>\n<\/li>\n
          5. Here is how it looks like\n
            \"Call<\/a>
            Call Youtube API in SSIS Example (Dynamic URL)<\/p><\/div><\/li>\n<\/ol>\n
            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.<\/p>\n
            Read YouTube Playlists in SSIS<\/h2>\n
            Once we done creating OAuth Connection Manager we can move forward to read YouTube data inside Data Flow.<\/p>\n
            Configure SSIS JSON \/ REST API Source<\/p>\n
              \n
            1. Now, Drag and Drop SSIS\u00a0Data Flow Task<\/b>\u00a0from SSIS Toolbox.
              \n\"SSIS<\/li>\n
            2. Double click on the Data Flow task to see Data Flow designer surface.<\/li>\n
            3. From the SSIS toolbox drag and drop JSON Source on the dataflow designer surface.
              \n\"SSIS<\/li>\n
            4. 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\n
              https:\/\/www.googleapis.com\/youtube\/v3\/playlists?part=snippet,id&maxResults=50&channelId={{User::varChannelId}}\r\n--OR--\u00a0\r\nhttps:\/\/www.googleapis.com\/youtube\/v3\/playlists?part=snippet,id&maxResults=50&channelId=xxxxxxxxxxxxxxxxxxxxxx<\/pre>\n<\/li>\n
            5. Check Use Credentials and select existing OAuth connection or Create New one<\/li>\n
            6. Select Array Filter (Items node) or type $.items[*]<\/span>\u00a0\u00a0as below.\n
            7. That’s it you can now Click Preview<\/strong> to see sample data. In the next section we will configure Pagination to read all records if you have more than 100 rows.<\/li>\n<\/ol>\n
              Configure YouTube REST API Pagination<\/h3>\n
              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<\/a>. Here is how to configure YouTube REST API Pagination.<\/p>\n
                \n
              1. Go to Pagination Tab found on JSON Source and select following 3 settings<\/li>\n
              2. Select Pagination Mode as Response Attribute Mode<\/span><\/li>\n
              3. Enter Next Link as $.nextPageToken<\/span><\/li>\n
              4. Enter Suffix for Next URL as &pageToken=<%nextlink%><\/span><\/li>\n<\/ol>\n
                \"YouTube<\/a>
                YouTube REST API Pagination Settings<\/p><\/div>\n
                Read YouTube Playlist Videos in SSIS<\/h2>\n
                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<\/a>.<\/p>\n
                For that everything should remain same as previous section except change URL as below. Where\u00a0xxxxxxxxxxxxxxxxxxxxx is your Playlist ID obtained from Previous Call.<\/p>\n
                https:\/\/www.googleapis.com\/youtube\/v3\/playlistItems?part=snippet,id&maxResults=50&playlistId=xxxxxxxxxxxxxxxxxxxxx<\/pre>\n
                Get all Videos for YouTube Channel<\/h2>\n
                If you want to get all video name and ID for specific channel then use below URL. Click here to read more<\/a>. Replace xxxxxxxxxxxxxxxxxxx with your Channel ID.<\/p>\n
                https:\/\/www.googleapis.com\/youtube\/v3\/search?channelId=xxxxxxxxxxxxxxxxxxx&part=snippet,id&order=date&maxResults=50<\/pre>\n
                 <\/p>\n
                Read YouTube Video Views, Likes, Dislikes (Extended Videos)<\/h2>\n
                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<\/a>\u00a0. 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<\/a>.<\/p>\n
                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)<\/a>.<\/p>\n
                  \n
                1. Get all videos for your channel by calling (Assuming you will paginate in below request.\n
                  https:\/\/www.googleapis.com\/youtube\/v3\/search?channelId=xxxxxxxxxxxxxxxxxxx&part=snippet,id&order=date&maxResults=50<\/pre>\n<\/li>\n
                2. 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.\n
                  https:\/\/www.googleapis.com\/youtube\/v3\/videos?part=snippet,contentDetails,statistics&id=ID1,ID2,ID3........ID25\r\nhttps:\/\/www.googleapis.com\/youtube\/v3\/videos?part=snippet,contentDetails,statistics&id=ID26,ID27,ID28........ID50\r\n........\r\n........\r\nhttps:\/\/www.googleapis.com\/youtube\/v3\/videos?part=snippet,contentDetails,statistics&id=ID176,ID177,ID178........ID200<\/pre>\n<\/li>\n
                3. Parse response coming from each Request Above…. Each Response will give you details about 25 videos you requesting.<\/li>\n<\/ol>\n
                  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.<\/p>\n
                  We have used Following Components to achieve Bulk Mode<\/p>\n