{"id":7217,"date":"2019-06-13T21:04:57","date_gmt":"2019-06-13T21:04:57","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=7217"},"modified":"2024-09-25T13:50:14","modified_gmt":"2024-09-25T13:50:14","slug":"import-bing-ads-data-sql-server-performance-reports","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/import-bing-ads-data-sql-server-performance-reports\/","title":{"rendered":"Import Bing Ads data into SQL Server (Performance Reports)"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>In our previous blog post we saw how to import REST \/ SOAP API in SQL Server<\/a>. Using same concepts let’s look at how to import Bing Ads data into SQL Server. We will explore many techniques to call Bing Ads API and learn how to automate data extraction without doing any ETL. You can call Bing Ads API just using T-SQL code (Yes you heard it right). At the end of this article you will learn how to Download Performance Reports from Bing Ads Account<\/strong> without any coding (See sample screenshot below). Please go through full article carefully.<\/p>\n

NOTE:<\/strong> Bing Ads now renamed as Microsoft Advertising but this article will use popular name for time being.<\/p>\n

<\/h2>\n
\"Import<\/a>

Import Bing Ads data into SQL Server (Using SSMS T-SQL Code)<\/p><\/div>\n

 <\/p>\n

 <\/p>\n

Requirements<\/h2>\r\nThis article talks about few tools and techniques in order to load API data in SQL Server. Please make sure following prerequisites are met.\r\n
    \r\n \t
  1. Download and Install ZappySys ODBC PowerPack<\/a> (This includes XML \/ JSON \/ REST API and few other drivers for SQL Server and ODBC connectivity in tools like Excel, Power BI, SSRS)<\/li>\r\n \t
  2. Make sure you have access to SQL Server Instance. If you cant find one still want to try what is mentioned in this article then install FREE SQL Express Edition<\/a><\/li>\r\n \t
  3. Confirm that you have SSMS Installed. If you don't have then you can download from here<\/a>.<\/li>\r\n<\/ol><\/div>\n

    About Bing Ads API \/ SOAP Web Service<\/h2>\n

    If you are new to Bing Ads API then start from here<\/a>. Bing Ads APIs are SOAP XML APIs. You can call it to automate many scenarios<\/a> for Bing Ads. In this article we will mainly focus on read scenario (e.g. Read Performance Data) but you can use these techniques to write \/ update (e.g. Create New Campaign).<\/p>\n

    If you are new to SOAP WebService then Check this article<\/a> to learn more. It will explain you\u00a0how to use tools like SoapUI<\/strong> tool to craft SOAP Requests for Bing API or any other SOAP API.<\/p>\n

    For Bing API you can use Service WSDL files found here<\/a>. Use URL ending ?wsdl<\/strong> to import in SoapUI.<\/p>\n

    Example Bing API Request<\/h2>\n

    Here is raw API request for Bing API. This sample assumes you have completed 3-Legged OAuth Authorization to obtain AccessToken and RefreshToken (Explained later in this article). Below request uses AccessToken you might have already obtained via process like this<\/a>. If you are using ZappySys tools then this will be generated automatically for you.<\/p>\n

    Request Method \/ URL:<\/strong><\/p>\n

    POST https:\/\/clientcenter.api.bingads.microsoft.com\/Api\/CustomerManagement\/v13\/CustomerManagementService.svc<\/pre>\n
    Headers:
    \n<\/strong>Notice that for each Api you may need correct SOAPAction<\/strong> in Header. If you are not sure then use SoapUI Tool. Execute request in SoapUI and then go to Raw tab <\/strong>    like this one<\/a>.<\/p>\n
    Content-Type: text\/xml; charset=utf-8\r\nSOAPAction: \"GetUser\"<\/pre>\n
    Body:
    \n<\/strong>Notice that you have to replace AuthenticationToken<\/strong> and DeveloperToken<\/strong> in below request. AccessToken is placed inside AuthenticationToken tag. This Auth token is short lived and extracted via OAuth Process and DeveloperToken you can extract as mentioned in the previous section     or get it from here<\/a>.<\/p>\n
    \r\n  \r\n    GetUser<\/Action>\r\n    <%access_token%><\/AuthenticationToken>\r\n    1052Bxxxxxxxxxxxxxx<\/DeveloperToken>\r\n  <\/s:Header>\r\n  \r\n    \r\n      <\/UserId>\r\n    <\/GetUserRequest>\r\n  <\/s:Body>\r\n<\/s:Envelope><\/pre>\n
    Response:<\/strong><\/p>\n
    <s:Envelope xmlns:s=\"http:\/\/schemas.xmlsoap.org\/soap\/envelope\/\">\r\n  <s:Header>\r\n    <h:TrackingId xmlns:h=\"https:\/\/bingads.microsoft.com\/Customer\/v13\">fddf3cb9-e4c2-4e75-9546-f3bc20ea91a8<\/h:TrackingId>\r\n  <\/s:Header>\r\n  <s:Body>\r\n    <GetUserResponse xmlns=\"https:\/\/bingads.microsoft.com\/Customer\/v13\">\r\n      <User xmlns:a=\"https:\/\/bingads.microsoft.com\/Customer\/v13\/Entities\" xmlns:i=\"http:\/\/www.w3.org\/2001\/XMLSchema-instance\">\r\n        <a:ContactInfo>\r\n          <a:Address>\r\n            <a:City i:nil=\"true\" \/>\r\n            <a:CountryCode>US<\/a:CountryCode>\r\n            <a:Id i:nil=\"true\" \/>\r\n            <a:Line1 i:nil=\"true\" \/>\r\n            <a:Line2 i:nil=\"true\" \/>\r\n            <a:Line3 i:nil=\"true\" \/>\r\n            <a:Line4 i:nil=\"true\" \/>\r\n            <a:PostalCode i:nil=\"true\" \/>\r\n            <a:StateOrProvince i:nil=\"true\" \/>\r\n            <a:TimeStamp i:nil=\"true\" \/>\r\n            <a:BusinessName i:nil=\"true\" \/>\r\n          <\/a:Address>\r\n          <a:ContactByPhone>false<\/a:ContactByPhone>\r\n          <a:ContactByPostalMail>false<\/a:ContactByPostalMail>\r\n          <a:Email>someone@zappysys.com<\/a:Email>\r\n          <a:EmailFormat i:nil=\"true\" \/>\r\n          <a:Fax i:nil=\"true\" \/>\r\n          <a:HomePhone i:nil=\"true\" \/>\r\n          <a:Id>48045678<\/a:Id>\r\n          <a:Mobile i:nil=\"true\" \/>\r\n          <a:Phone1>111-222-3333<\/a:Phone1>\r\n          <a:Phone2 i:nil=\"true\" \/>\r\n        <\/a:ContactInfo>\r\n        <a:CustomerId>19112345<\/a:CustomerId>\r\n        <a:Id>48012345<\/a:Id>\r\n        <a:JobTitle i:nil=\"true\" \/>\r\n        <a:LastModifiedByUserId>48012345<\/a:LastModifiedByUserId>\r\n        <a:LastModifiedTime>2019-06-04T14:22:35.38<\/a:LastModifiedTime>\r\n        <a:Lcid>EnglishUS<\/a:Lcid>\r\n        <a:Name>\r\n          <a:FirstName>Someone<\/a:FirstName>\r\n          <a:LastName>Good<\/a:LastName>\r\n          <a:MiddleInitial i:nil=\"true\" \/>\r\n        <\/a:Name>\r\n        <a:Password i:nil=\"true\" \/>\r\n        <a:SecretAnswer i:nil=\"true\" \/>\r\n        <a:SecretQuestion>None<\/a:SecretQuestion>\r\n        <a:UserLifeCycleStatus>Active<\/a:UserLifeCycleStatus>\r\n        <a:TimeStamp>AAAAAJiX\/cU=<\/a:TimeStamp>\r\n        <a:UserName>someone@zappysys.com<\/a:UserName>\r\n        <a:ForwardCompatibilityMap i:nil=\"true\" xmlns:b=\"http:\/\/schemas.datacontract.org\/2004\/07\/System.Collections.Generic\" \/>\r\n      <\/User>\r\n      <CustomerRoles xmlns:a=\"https:\/\/bingads.microsoft.com\/Customer\/v13\/Entities\" xmlns:i=\"http:\/\/www.w3.org\/2001\/XMLSchema-instance\">\r\n        <a:CustomerRole>\r\n          <a:RoleId>41<\/a:RoleId>\r\n          <a:CustomerId>19160000<\/a:CustomerId>\r\n          <a:AccountIds i:nil=\"true\" xmlns:b=\"http:\/\/schemas.microsoft.com\/2003\/10\/Serialization\/Arrays\" \/>\r\n          <a:LinkedAccountIds xmlns:b=\"http:\/\/schemas.microsoft.com\/2003\/10\/Serialization\/Arrays\" \/>\r\n          <a:CustomerLinkPermission i:nil=\"true\" \/>\r\n        <\/a:CustomerRole>\r\n      <\/CustomerRoles>\r\n    <\/GetUserResponse>\r\n  <\/s:Body>\r\n<\/s:Envelope><\/pre>\n
    Things to know about REST \/ SOAP API<\/h2>\r\nBefore we deep dive into calling your specific APIs you like to integrate, you must have basic understanding what is REST API \/ SOAP API. In this section we will cover few important concepts and difference about these two API styles REST vs SOAP.\r\n
    What is REST API (or RESTful API)<\/strong><\/h3>\r\nAs per wikipedia REST<\/a> is an architecture style which can be used to to expose data over the internet using http \/ https. Using REST style you can expose data operations \/ functions which can be called by other users. With API calls You can read \/ write data or just call business functions which triggers some workflow.\r\n\r\nMost APIs documents various aspects of calling API (REST or SOAP). You need to pass these details accurately. Refer to your\u00a0API documentation like this one for example<\/a>\u00a0. If its an internal API then contact API developer to get more details.\r\n