{"id":8128,"date":"2019-10-09T20:22:28","date_gmt":"2019-10-09T20:22:28","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=8128"},"modified":"2019-10-11T16:29:10","modified_gmt":"2019-10-11T16:29:10","slug":"get-data-google-search-console-api-ssis-odbc-drivers","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/get-data-google-search-console-api-ssis-odbc-drivers\/","title":{"rendered":"Get data from Google Search Console API in SSIS and ODBC Apps"},"content":{"rendered":"

Introduction<\/h2>\n

In our previous few posts we saw how to call various google apis<\/a> in SSIS.\u00a0 In this post lets learn how to call Google Search Console API in SSIS or other ODBC Compatible Apps such as Power BI, Informatica, SSRS using API Drivers for ODBC<\/a><\/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

    About Google Search Console API (Google Webmaster API)<\/h2>\n

    If you are new to Google Search Console API (i.e. Webmaster API) then start from this link<\/a>. You can also check API Explorer<\/a> here to test API requests.<\/p>\n

    Search API can be called by supplying either API key<\/a> in URL\u00a0 or use OAuth<\/a><\/p>\n

    Search Console V3 Covers following APIs (May add more in future)<\/p>\n

    \n\n\n\n\n\n\n\n\n\n\n\n\n
    API Name<\/span><\/strong><\/td>\nDescription<\/span><\/strong><\/td>\n<\/tr>\n
    webmasters.searchanalytics.query<\/span><\/td>\nQuery your data with filters and parameters that you define. Returns zero or more rows grouped by the row keys that you define. You must define a date range of one or more days. When date is one of the group by values, any days without data are omitted from the result list. If you need to know which days have data, issue a broad date range query grouped by date for any metric, and see which day rows are returned.<\/span><\/td>\n<\/tr>\n
    webmasters.sitemaps.delete<\/span><\/td>\nDeletes a sitemap from this site.<\/span><\/td>\n<\/tr>\n
    webmasters.sitemaps.get<\/span><\/td>\nRetrieves information about a specific sitemap.<\/span><\/td>\n<\/tr>\n
    webmasters.sitemaps.list<\/span><\/td>\nLists the sitemaps-entries submitted for this site, or included in the sitemap index file (if sitemapIndex is specified in the request).<\/span><\/td>\n<\/tr>\n
    webmasters.sitemaps.submit<\/span><\/td>\nSubmits a sitemap for a site.<\/span><\/td>\n<\/tr>\n
    webmasters.sites.add<\/span><\/td>\nAdds a site to the set of the user’s sites in Search Console.<\/span><\/td>\n<\/tr>\n
    webmasters.sites.delete<\/span><\/td>\nRemoves a site from the set of the user’s Search Console sites.<\/span><\/td>\n<\/tr>\n
    webmasters.sites.get<\/span><\/td>\nRetrieves information about specific site.<\/span><\/td>\n<\/tr>\n
    webmasters.sites.list<\/span><\/td>\nLists the user’s Search Console sites.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    Search Console API Call Example<\/strong><\/p>\n

    Here is simple example of Search Console API call. Below example returns all sites you added under your search console account.<\/p>\n

    Request<\/strong><\/p>\n

    GET https:\/\/www.googleapis.com\/webmasters\/v3\/sites?key={YOUR_API_KEY}<\/pre>\n
    Response<\/strong><\/p>\n
    {\r\n \"siteEntry\": [\r\n  {\r\n   \"siteUrl\": \"https:\/\/my-google-search-api-1.com\/\",\r\n   \"permissionLevel\": \"siteOwner\"\r\n  },\r\n  {\r\n   \"siteUrl\": \"https:\/\/www.my-google-search-api-1.com\/\",\r\n   \"permissionLevel\": \"siteOwner\"\r\n  },\r\n  {\r\n   \"siteUrl\": \"https:\/\/my-google-search-api-2.com\/\",\r\n   \"permissionLevel\": \"siteOwner\"\r\n  },\r\n  {\r\n   \"siteUrl\": \"https:\/\/www.my-google-search-api-2.com\/\",\r\n   \"permissionLevel\": \"siteOwner\"\r\n  }\r\n ]\r\n}\r\n\r\n<\/pre>\n
     <\/p>\n
    Step-By-Step – Call Search Console API in SSIS<\/h2>\n
    Now lets look at very simple API call to list search console sites<\/a><\/p>\n
      \n
    1. Open SSIS Package<\/li>\n
    2. Drag and drop ZS REST API Task from SSIS tool box\n
      \"Drag
      Drag and Drop ZS REST API Task from SSIS Toolbox<\/p><\/div><\/li>\n
    3. Now double click REST API Task and configure (Skip Step 5<\/strong>\u00a0if you use API Key<\/a> instead of OAuth).<\/li>\n
    4. Enter API URL you like to call. For This example we will use simple URL as below
      \nOAuth based Authentication<\/strong><\/p>\n
      https:\/\/www.googleapis.com\/webmasters\/v3\/sites\/<\/pre>\n
      API Key based Authentication<\/strong><\/p>\n
      https:\/\/www.googleapis.com\/webmasters\/v3\/sites\/?key={YOUR_API_KEY}<\/pre>\n<\/li>\n
    5. If you want to use OAuth based credentials then perform the following steps\n
        \n
      1. Select URL from Connection\u00a0<\/strong><\/li>\n
      2. Click New ZS-OAUTH<\/strong>\u00a0connection from Dropdown<\/li>\n
      3. On the OAuth UI select Google as Provider<\/strong><\/li>\n
      4. Enter Scopes as below\n
        https:\/\/www.googleapis.com\/auth\/webmasters\r\nhttps:\/\/www.googleapis.com\/auth\/webmasters.readonly<\/pre>\n<\/li>\n
      5. Click Generate Token. When you get Login Prompt enter your Google Account information and click Accept<\/strong><\/li>\n
      6. Click OK to Save OAuth UI<\/li>\n<\/ol>\n<\/li>\n
      7. This is how it will look like if you use OAuth Connection\n
      8. Now click Test Request \/ Response to check if its working. You should get response like below<\/li>\n<\/ol>\n
         <\/p>\n
        Read from Search Console Analytics API (query)<\/h2>\n
        Now let’s look at how to call Search Console Analytics API (i.e. query)<\/a> for Specified Site and extract data in tabular format using ZS JSON Source<\/a>. In below example we will obtain search Impression, clicks, position etc for specified site. We will aggregate this over date and country (dimensions).<\/p>\n
        In this section you will learn how to use JSON Source Adapter to extract data from JSON file (In this case its Web URL).<\/p>\n
          \n
        1. Firstly, You need to\u00a0Download and Install<\/b>\u00a0SSIS\u00a0ZappySys PowerPack.<\/a><\/li>\n
        2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.<\/li>\n
        3. Now, Drag and Drop SSIS\u00a0Data Flow Task<\/b>\u00a0from SSIS Toolbox.
          \n\"SSIS<\/li>\n
        4. Double click on the Data Flow task to see Data Flow designer surface.<\/li>\n
        5. From the SSIS toolbox drag and drop JSON Source on the Data Flow designer surface.
          \n\"SSIS<\/li>\n
        6. Double click JSON Source and configure like below\n
            \n
          1. Enter URL as below. Notice two things, first we used <<somedata,FUN_URLENC>><\/strong> placeholder function<\/a> to encode :<\/strong> and \/\/<\/strong> in the URL so it become %2F %3A<\/a>. We use –dont-escape–<\/span>\u00a0 suffix at the end.
            \nFor OAuth based Credentials<\/strong><\/p>\n
            https:\/\/www.googleapis.com\/webmasters\/v3\/sites\/<<https:\/\/mysite.com,FUN_URLENC>>\/searchAnalytics\/query--dont-escape--<\/pre>\n
            For API Key based Credentials<\/strong><\/p>\n
            https:\/\/www.googleapis.com\/webmasters\/v3\/sites\/<<https:\/\/mysite.com,FUN_URLENC>>\/searchAnalytics\/query\/?key={YOUR_API_KEY}--dont-escape--<\/pre>\n<\/li>\n
          2. Check Use Credentials and select OAuth connection (created earlier) (if you are using API key in URL then skip this step).<\/li>\n
          3. Select POST<\/strong> as HTTP Method<\/li>\n
          4. Enter Body for Query. Here are some examples<\/a> for various queries you can use. For our case we want to group by date and country so use below\n
            {\r\n\"startDate\": \"2019-01-01\",\r\n\"endDate\": \"2019-01-10\",\r\n\"dimensions\" : [\"date\",\"country\"]\r\n}<\/pre>\n<\/li>\n
          5. Select Content Type<\/strong> as application\/json<\/span>\u00a0 from Content Type drop down.<\/li>\n
          6. Select Filter or enter as\u00a0 $.rows[*]<\/span><\/li>\n
          7. Now go to Extract Multiple Arrays Tab and Check Array Flattening Option<\/strong> (on older version it was on the different Tab)<\/li>\n<\/ol>\n<\/li>\n
          8. Click Preview and you will see your Google Search Console API data as below.\n
            \"Read<\/a>
            Read from Google Search API Console (Analytics Query Endpoint) – JSON Source<\/p><\/div><\/li>\n<\/ol>\n
            Loading Google Search Console API data into SQL Server \/ Other Target<\/h2>\n
            ZappySys SSIS PowerPack makes it easy to load data from various sources such as REST, SOAP, JSON, XML, CSV or from other source into SQL Server, or PostgreSQL, or Amazon Redshift, or other  targets. The Upsert Destination<\/strong> component allows you to automatically insert new records and update existing ones based on key columns. Below are the detailed steps to configure it.<\/p>\r\n
            Step 1: Add Upsert Destination to Data Flow<\/h3>\r\n
              \r\n
            1. Drag and drop the Upsert Destination<\/strong> component from the SSIS Toolbox.<\/li>\r\n
            2. Connect your source component (e.g., JSON \/ REST \/ Other Source) to the Upsert Destination.<\/li>\r\n<\/ol>\r\n
              \r\n\r\n\"\"<\/a>\r\n
              SSIS - Data Flow - Drang and Drop Upsert Destination Component<\/p>\r\n<\/div>\r\n
              Step 2: Configure Target Connection<\/h3>\r\n
                \r\n
              1. Double-click the Upsert Destination<\/strong> component to open the configuration window.<\/li>\r\n
              2. Under Connection<\/strong>, select an existing target connection or click NEW<\/strong> to create a new connection.\r\n