Import Bing Ads data into SQL Server (Performance Reports)


In our previous blog post we saw how to import REST / SOAP API in SQL Server. 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 without any coding (See sample screenshot below). Please go through full article carefully.

NOTE: Bing Ads now renamed as Microsoft Advertising but this article will use popular name for time being.

Import Bing Ads data into SQL Server (Using SSMS T-SQL Code)

Import Bing Ads data into SQL Server (Using SSMS T-SQL Code)




This article talks about few tools and techniques in order to load API data in SQL Server. Please make sure following prerequisites are met.
  1. Download and Install ZappySys ODBC PowerPack (This includes XML / JSON / REST API and few other drivers for SQL Server and ODBC connectivity in tools like Excel, Power BI, SSRS)
  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
  3. Confirm that you have SSMS Installed. If you don't have then you can download from here.

About Bing Ads API / SOAP Web Service

If you are new to Bing Ads API then start from here. Bing Ads APIs are SOAP XML APIs. You can call it to automate many scenarios 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).

If you are new to SOAP WebService then Check this article to learn more. It will explain you how to use tools like SoapUI tool to craft SOAP Requests for Bing API or any other SOAP API.

For Bing API you can use Service WSDL files found here. Use URL ending ?wsdl to import in SoapUI.

Example Bing API Request

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. If you are using ZappySys tools then this will be generated automatically for you.

Request Method / URL:

Notice that for each Api you may need correct SOAPAction in Header. If you are not sure then use SoapUI Tool. Execute request in SoapUI and then go to Raw tab like this one.

Notice that you have to replace AuthenticationToken and DeveloperToken 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.


Things to know about REST / SOAP API

Before 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.

What is REST API (or RESTful API)

As per wikipedia REST 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. Most APIs documents various aspects of calling API (REST or SOAP). You need to pass these details accurately. Refer to your API documentation like this one for example . If its an internal API then contact API developer to get more details.
  • URL and Parameters  (or sometimes referred as endpoint)
  • Request Method (or sometimes referred as Verb)- e.g. GET, POST, PUT, PATCH .....
  • HTTP Headers
  • Request Body
Sample REST API Call Request:
POST http://myhost/api/v2/customers/new
X-SecretKey: Abcdxxxxxxxxxxxxxx
Content-Type: application/json

{ firstname: "Someone", lastname: "Good" }
 request_status: "CreatedOK",
 new_customer_id: "1234566"

What is SOAP Web Service (XML API)

SOAP Web Service is an old standard developed before REST Style got popular. SOAP is strictly XML based API on the other hand RESTful API can be any format. As we said earlier REST is an architecture style,  and SOAP is a standard (XML Based Protocol to transfer data). For more information on SOAP API refer to this link / videos

Other Concepts Calling REST / SOAP

There are few things you have to find out from your API Provider (Read their documentation like this for example). Few important concepts for API calling listed below.
  • Authentication - Most APIs need some sort of credentials to verify your identity. Here are some common Auth mechanism
  • Pagination - Most APIs don't return huge amount of data in a single requests so you must call next request to continue fetch more data until all rows are fetched. There is no standard around pagination so find out from your API provider how to paginate. Check this article for example to learn about various pagination patterns.
  • Throttling (API calls rate limit) - Most API providers enforce API call rate limit. Which means you cannot call it more than X times in a given time. If you ever face such issue, You can enable error retry option like this or simply add delay like this to slow down.

Must have Tools for API Testing / Debugging

Before you start integrating API calls in ZappySys products like ODBC PowerPack or SSIS PowerPack you can start testing API using tools like CURL, Postman or Fiddler.


Fiddler is the best API API debugger out there. It acts like a small Proxy server so you can see all Web requests on your system in a raw format. You can also use this tool to Test API requests (See Composer tab in Fiddler) however if API testing is your primary use case then use POSTMAN (see next) because it has more UI elements compared to Fiddler. Here is small tutorial to get started with Fiddler.


Postman is probably the most popular tool to test API with full User interface. It's not data processing like ZappySys but you can pass various parts of API we talked earlier and test API before you can start using ZappySys for Data integration. Here is Video Tutorial to get started with Postman.


CURL is a simple yet most popular command line tool to call / test APIs. It doesn't have UI elements like previous two tools so have to learn about each options you can pass to this command line.

Step-By-Step: How to Import Bing Ads Data into SQL Server

Now let’s look at how to import Bing Ads Data into SQL Server Table. This tutorial will require 10-15 mins of your time. At the end of this tutorial you will be able to Read Bing Ads Performance Report and Import data into SQL Server. Step listed in this tutorial may help you to call Bing API in other tools like SSIS, Power BI, SSRS, Tableau and so on.

So let’s get started.

Register OAuth App for Bing Ads API (Azure Active Directory App)

Very first thing to call any Bing API is to register OAuth App (Under Azure Active Directory Tab).

Here is how to register OAuth App which can be used to call Bing Ads API later on.

  1. Go to Azure Portal - App Registration. Click New Registration.
    Register new OAuth App in Azure Portal (Active Directory Tab)

    Register new OAuth App in Azure Portal (Active Directory Tab)

  2. Select App Type as below
    Azure OAuth App Registration - Select Type

    Azure OAuth App Registration - Select Type

  3. Once App is created go to Authentication tab. Under Suggested Redirect URIs check below URI and click Save Button in Toolbar.
  4. Click on Overview Tab and copy Application Id somewhere (This is needed for OAuth)

Get Bing API Developer Token

Once we have OAuth App registered next thing is to obtain Developer Token for Bing Ads API. Click here to request new token or view existing Developer token.

Configure ZappySys Data Gateway

Now let's look at steps to configure Data Gateway after installation.
  1. Assuming you have installed ZappySys ODBC PowerPack using default options (Which also enables Data Gateway Service)
  2. Search "Gateway" in your start menu and click ZappySys Data Gateway
    Open ZappySys Data Gateway

    Opening ZappySys Data Gateway

  3. First, make sure Gateway Service is running (Verify Start icon is disabled)
  4. Also, verify Port on General Tab
    Port Number setting on ZappySys Data Gateway

    Checking port number setting on ZappySys Data Gateway

  5. Now go to Users tab. Click Add icon to add a new user. Check Is admin to give access to all data sources you add in future. If you don't check admin then you have to manually configure user permission for each data source.

    Adding the Gateway user


Create new Bing Ads API Data Source in Gateway

Once you setup Data Gateway user we can move to next step. To call Bing API we need to setup Connection for XML Driver in ZappySys Data Gateway.

  1. Click on Add New Data source. Name as BingAds and select Native – ZappySys XML Driver option.
    Add Gateway Data Source

    Add Gateway Data Source

  2. Now click Edit to Configure Settings. For now we will only care OAuth Connection settings. All other options are overwritten in SQL Query.
    Edit Gateway Data Source Settings

    Edit Gateway Data Source Settings

  3. When UI opens Click on Load ConnectionString and Paste below connection string after changing some items (Screenshot may be differ). Make sure to replace following attributes.RefreshTokenFilePath – Enter some valid file path which will hold refresh token
    ClientId – get Application Id from Azure Portal under App we created earlier)
    DeveloperToken – Replace 1052Bxxxxxxxxxxxx with your own devloper token.

    Load ZappySys Driver ConnectionString to configure UI

    Load ZappySys Driver ConnectionString to configure UI

    Use below string and replace parameters 

  4. Next to OAuth Click on Configure Settings
  5. By default most of settings on OAuth connections should be pre-populated for you but still lets Notice few things. This is how to setup redirect URI
  6. Configure Redirect URI for Bing Ads API OAuth Connection

    Configure Redirect URI for Bing Ads API OAuth Connection

  7. Here is where you setup Changing Refresh Token File path. if you dont supply this path then your refresh token extracted first time will expire in 60 days.
    Refresh Token File path (Only needed if API issues new Refresh Token when you request AccessToken)

    Refresh Token File path (Only needed if API issues new Refresh Token when you request AccessToken)

  8. Once you verify all settings click Generate Token and Finish the process. It will populate AccessToken and RefreshToken Fields if everything goes well.
    Configure Bing Ads API Connection (OAuth 2.0)

    Configure Bing Ads API Connection (OAuth 2.0)

  9. Click OK to save OAuth Connection UI to go back to main UI
  10. You can now Click Test Connection to confirm everything
  11. If you need to access Gateway from Other machine then go to Firewall tab and Click Add Rule
  12. Click Save button on Gateway UI to save and restart the service.

Create a new Linked Server for Bing Ads API

Once we setup setup Bing Ads Data Source in Gateway we can move to next step which is create a Linked Server which points to Data gateway Data source we created. This will create a bridge between ZappySys Driver and SQL Server so you can call API via T-SQL.

  1. If you are using Latest ODBC PowerPack then on Data gateway UI you will See App Integration Tab. Copy Code and run it in SSMS to create a new linked server.
    ZappySys Data Gateway code generator for Linked Server

    ZappySys Data Gateway code generator for Linked Server

  2. If things go well when you execute code you may see some result in grid.

Run sample Queries to call Bing API in SQL Server / Import Data

Once Linked Server is created we can run various queries like below.






Download Bing Ads Performance Data in SQL Server Table

Now lets look at more complex example to read data from Bing Performance Report. Calling This API is tricky due to many steps involved in data extraction. But not to worry if you use ZappySys Drivers. This is API is somewhat similar as Amazon MWS Report Request (See last section).

Here are high level steps needs to be performed to call API for Bing Ads Report (e.g. Performance Report)

  1. Call SubmitGenerateReportResponse API to request new Report. This returns ReportRequestId
  2. Now we have to keep checking every few seconds see report is ready or not. Call PollGenerateReport until Status field is Success from Pending
  3. Once previous step returns Success extract Report Download URL (Its Zip File which may contain data in CSV or XML)

So lets get started to implement above login in a single T-SQL Query and load data into Table. There is a known limitation with Query Size we can send to data gateway from SSMS so we can use some tricks to read Body from File rather than hard coding in SQL. We can use IsMultiPart=True and then supply Filepath in RequestBody (Path must start with @ symbol)

  1. Bing Report API needs 2 additional values along with DeveloperToken we got earlier. You need to supply AccountId and CustomerId part of Body so obtain it by visiting below page in your bing account.

    Obtain Bing Ads AccountId, ClientId for APi calls

    Obtain Bing Ads AccountId, ClientId for APi calls

  2. Create new XML file for first step in a Notepad. Enter below content and save as C:\Requests\BingAds\sql_body1.xml
    Replace Following Values as per your settingsCustomerAccountId (Found 2 places)
  3. Create another XML file for second step in a Notepad. Enter below content and save as C:\Requests\BingAds\sql_body2.xml
    Replace following values as per your need.
  4. Now try to execute below query. This should generate Performance report and download it in a SQL table for you.

    Import Bing Ads data into SQL Server

Multi Step Query to download Report

So in previous section we saw how to write a single query to get your Performance Report from Bing Ads. However there will be time you want to split 3 steps into seperate queries.

Here is how to do.



In this article we saw how to import Bing Ads API data in SQL Server without any coding using ZappySys ODBC PowerPack (SOAP / XML Driver). Download it it and try it for FREE to explore many API integration features not discussed in this article.

Posted in ODBC Gateway, REST API Integration, T-SQL (SQL Server), XML File / SOAP API Driver and tagged , , .