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)

Requirements

This article talks about few tools and techniques in order to load API data in SQL Server. Please make sure following prerequisites are met.

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)
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
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:

POST https://clientcenter.api.bingads.microsoft.com/Api/CustomerManagement/v13/CustomerManagementService.svc

1	POST https://clientcenter.api.bingads.microsoft.com/Api/CustomerManagement/v13/CustomerManagementService.svc

Headers:
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.

Content-Type: text/xml; charset=utf-8
SOAPAction: "GetUser"

1 2	Content-Type: text/xml; charset=utf-8 SOAPAction: "GetUser"

Body:
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.

<s:Envelope xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Header xmlns="https://bingads.microsoft.com/Customer/v13">
    <Action mustUnderstand="1">GetUser</Action>
    <AuthenticationToken i:nil="false"><%access_token%></AuthenticationToken>
    <DeveloperToken i:nil="false">1052Bxxxxxxxxxxxxxx</DeveloperToken>
  </s:Header>
  <s:Body>
    <GetUserRequest xmlns="https://bingads.microsoft.com/Customer/v13">
      <UserId i:nil="true"></UserId>
    </GetUserRequest>
  </s:Body>
</s:Envelope>

<s:Envelope xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">

<s:Header xmlns="https://bingads.microsoft.com/Customer/v13">

<Action mustUnderstand="1">GetUser</Action>

<DeveloperToken i:nil="false">1052Bxxxxxxxxxxxxxx</DeveloperToken>

</s:Header>

<s:Body>

</GetUserRequest>

</s:Body>

</s:Envelope>

Response:

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Header>
    <h:TrackingId xmlns:h="https://bingads.microsoft.com/Customer/v13">fddf3cb9-e4c2-4e75-9546-f3bc20ea91a8</h:TrackingId>
  </s:Header>
  <s:Body>
    <GetUserResponse xmlns="https://bingads.microsoft.com/Customer/v13">
      <User xmlns:a="https://bingads.microsoft.com/Customer/v13/Entities" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
        <a:ContactInfo>
          <a:Address>
            <a:City i:nil="true" />
            <a:CountryCode>US</a:CountryCode>
            <a:Id i:nil="true" />
            <a:Line1 i:nil="true" />
            <a:Line2 i:nil="true" />
            <a:Line3 i:nil="true" />
            <a:Line4 i:nil="true" />
            <a:PostalCode i:nil="true" />
            <a:StateOrProvince i:nil="true" />
            <a:TimeStamp i:nil="true" />
            <a:BusinessName i:nil="true" />
          </a:Address>
          <a:ContactByPhone>false</a:ContactByPhone>
          <a:ContactByPostalMail>false</a:ContactByPostalMail>
          <a:Email>someone@zappysys.com</a:Email>
          <a:EmailFormat i:nil="true" />
          <a:Fax i:nil="true" />
          <a:HomePhone i:nil="true" />
          <a:Id>48045678</a:Id>
          <a:Mobile i:nil="true" />
          <a:Phone1>111-222-3333</a:Phone1>
          <a:Phone2 i:nil="true" />
        </a:ContactInfo>
        <a:CustomerId>19112345</a:CustomerId>
        <a:Id>48012345</a:Id>
        <a:JobTitle i:nil="true" />
        <a:LastModifiedByUserId>48012345</a:LastModifiedByUserId>
        <a:LastModifiedTime>2019-06-04T14:22:35.38</a:LastModifiedTime>
        <a:Lcid>EnglishUS</a:Lcid>
        <a:Name>
          <a:FirstName>Someone</a:FirstName>
          <a:LastName>Good</a:LastName>
          <a:MiddleInitial i:nil="true" />
        </a:Name>
        <a:Password i:nil="true" />
        <a:SecretAnswer i:nil="true" />
        <a:SecretQuestion>None</a:SecretQuestion>
        <a:UserLifeCycleStatus>Active</a:UserLifeCycleStatus>
        <a:TimeStamp>AAAAAJiX/cU=</a:TimeStamp>
        <a:UserName>someone@zappysys.com</a:UserName>
        <a:ForwardCompatibilityMap i:nil="true" xmlns:b="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
      </User>
      <CustomerRoles xmlns:a="https://bingads.microsoft.com/Customer/v13/Entities" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
        <a:CustomerRole>
          <a:RoleId>41</a:RoleId>
          <a:CustomerId>19160000</a:CustomerId>
          <a:AccountIds i:nil="true" xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays" />
          <a:LinkedAccountIds xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays" />
          <a:CustomerLinkPermission i:nil="true" />
        </a:CustomerRole>
      </CustomerRoles>
    </GetUserResponse>
  </s:Body>
</s:Envelope>

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">

<s:Header>

<h:TrackingId xmlns:h="https://bingads.microsoft.com/Customer/v13">fddf3cb9-e4c2-4e75-9546-f3bc20ea91a8</h:TrackingId>

</s:Header>

<s:Body>

<a:ContactInfo>

<a:Address>

<a:City i:nil="true" />

<a:CountryCode>US</a:CountryCode>

<a:Id i:nil="true" />

<a:Line1 i:nil="true" />

<a:Line2 i:nil="true" />

<a:Line3 i:nil="true" />

<a:Line4 i:nil="true" />

<a:PostalCode i:nil="true" />

<a:StateOrProvince i:nil="true" />

<a:TimeStamp i:nil="true" />

<a:BusinessName i:nil="true" />

</a:Address>

<a:ContactByPhone>false</a:ContactByPhone>

<a:ContactByPostalMail>false</a:ContactByPostalMail>

<a:Email>someone@zappysys.com</a:Email>

<a:EmailFormat i:nil="true" />

<a:Fax i:nil="true" />

<a:HomePhone i:nil="true" />

<a:Id>48045678</a:Id>

<a:Mobile i:nil="true" />

<a:Phone1>111-222-3333</a:Phone1>

<a:Phone2 i:nil="true" />

</a:ContactInfo>

<a:CustomerId>19112345</a:CustomerId>

<a:Id>48012345</a:Id>

<a:JobTitle i:nil="true" />

<a:LastModifiedByUserId>48012345</a:LastModifiedByUserId>

<a:LastModifiedTime>2019-06-04T14:22:35.38</a:LastModifiedTime>

<a:Lcid>EnglishUS</a:Lcid>

<a:Name>

<a:FirstName>Someone</a:FirstName>

<a:LastName>Good</a:LastName>

<a:MiddleInitial i:nil="true" />

</a:Name>

<a:Password i:nil="true" />

<a:SecretAnswer i:nil="true" />

<a:SecretQuestion>None</a:SecretQuestion>

<a:UserLifeCycleStatus>Active</a:UserLifeCycleStatus>

<a:TimeStamp>AAAAAJiX/cU=</a:TimeStamp>

<a:UserName>someone@zappysys.com</a:UserName>

<a:ForwardCompatibilityMap i:nil="true" xmlns:b="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />

</User>

<a:CustomerRole>

<a:RoleId>41</a:RoleId>

<a:CustomerId>19160000</a:CustomerId>

<a:AccountIds i:nil="true" xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays" />

<a:LinkedAccountIds xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays" />

<a:CustomerLinkPermission i:nil="true" />

</a:CustomerRole>

</CustomerRoles>

</GetUserResponse>

</s:Body>

</s:Envelope>

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" }

Response:

{
 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
- Basic Authentication,
- OAuth 1a or 2.0
- Dynamic Token Auth
- Pass API key via URL Parameter e.g. http://myhost/api/v1/getcustomers?apikey=###some-secret-key####
- Pass API key via Header
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

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

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

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.

Login to Azure Portal:
- Navigate to the Azure Portal and log in using your credentials.
Access Azure Active Directory:
- In the left-hand menu, click on Azure Active Directory.
Register a New Application:
- Go to App registrations and click on New registration.
- Application Name: Enter a name for your application.
- Supported Account Types: Choose the account types your app will support. For example, select Accounts in this organizational directory only if you need access to data in your organization only.
- Redirect URI:
  - Set the type to Web.
  - In the textbox enter https://login.microsoftonline.com/common/oauth2/nativeclient as the Redirect URI or any other valid redirect URL, e.g., https://zappysys.com/oauth.
  - Use this Redirect URI in the Redirect URL grid row.
Save Client ID:
- After registering the app, copy the Application (client) ID and paste it into the Client ID field in the API Connection Manager configuration.
Set Authorization & Token URLs:
- Click on the Endpoints link in the App registration overview.
- Authorization URL: Copy the OAuth 2.0 authorization endpoint (v2) URL (e.g., https://login.microsoftonline.com/{your-tenant-id}/oauth2/v2.0/authorize) and paste it into the Authorization URL field in the configuration grid.
- Token URL: Copy the OAuth 2.0 token endpoint (v2) URL (e.g., https://login.microsoftonline.com/{your-tenant-id}/oauth2/v2.0/token) and paste it into the Token URL field.
Create a Client Secret:
- In the Certificates & secrets tab, click New client secret.
- Set an expiration period for the secret.
- Copy the generated client secret and paste it into the Client Secret field in the API Connection Manager configuration.
Configure API Permissions:
- Go to the API Permissions section.
- Click on Add a permission, select Microsoft Graph, and choose Delegated Permissions.
- Add the required permissions:
  - offline_access
  - openid
  - profile
  - Sites.Read.All
  - Sites.ReadWrite.All
  - User.Read
  - email
  Azure AD App – Required API Permissions
- Grant Admin Consent for the permissions that require it.
Generate Tokens:
- Use the Generate Token feature in the API Connection Manager to generate authentication tokens.
Use a Generic Account for Automation:
NOTE: If you are planning to use your current data connection/token for automated processes, we recommend that you use a generic account for token generation when the login box appears (e.g. sales_automation@mycompany.com instead of bob_smith@mycompany.com). When you use a personal account which is tied to a specific employee profile and that employee leaves the company, the token may become invalid and any automated processes using that token will fail. Another potentially unwanted effect of using a personal token is incorrect logging; the API calls (e.g. Read, Edit, Delete, Upload) made with that token will record the specific user as performing the calls instead of an automated process.
That’s it!

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.

Assuming you have installed ZappySys ODBC PowerPack using default options (Which also enables Data Gateway Service)
Search "Gateway" in your start menu and click ZappySys Data Gateway

Opening ZappySys Data Gateway
First, make sure Gateway Service is running (Verify Start icon is disabled)
Also, verify Port on General Tab

Checking port number setting on ZappySys Data Gateway
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.

Click on Add New Data source. Name as BingAds and select Native – ZappySys XML Driver option.
Add Gateway Data Source
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

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

Use below string and replace parameters

DRIVER={ZappySys XML Driver};
DataPath='https://clientcenter.api.bingads.microsoft.com/Api/CustomerManagement/v13/CustomerManagementService.svc';
DataConnectionType=OAuth;
AuthUrl='https://login.microsoftonline.com/common/oauth2/v2.0/authorize';
TokenUrl='https://login.microsoftonline.com/common/oauth2/v2.0/token';
ReturnUrl='https://login.microsoftonline.com/common/oauth2/nativeclient';
RefreshTokenFilePath='c:\xxxxxx_some_folder_xxxxxxx\BingAds_RefreshToken.txt';
ScopeSeparator='{space}';
Scope='https://ads.microsoft.com/ads.manage offline_access';
ClientId='xxxxxxxxxxxxxx';
UseCustomApp=True;
RequestData='<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Header>
    <h:ApplicationToken i:nil="true" xmlns:h="https://bingads.microsoft.com/Customer/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />
    <h:AuthenticationToken xmlns:h="https://bingads.microsoft.com/Customer/v13"><%access_token%></h:AuthenticationToken>
    <h:DeveloperToken xmlns:h="https://bingads.microsoft.com/Customer/v13">1052Bxxxxxxxxxxxx</h:DeveloperToken>
    <h:Password i:nil="true" xmlns:h="https://bingads.microsoft.com/Customer/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />
    <h:UserName i:nil="true" xmlns:h="https://bingads.microsoft.com/Customer/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />
  </s:Header>
  <s:Body>
    <GetUserRequest xmlns="https://bingads.microsoft.com/Customer/v13">
      <UserId i:nil="true" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />
    </GetUserRequest>
  </s:Body>
</s:Envelope>';
RequestContentTypeCode=TextXml;
RequestMethod='POST';
RequestHeaders='SOAPAction: "GetUser"'

DRIVER={ZappySys XML Driver};

DataPath='https://clientcenter.api.bingads.microsoft.com/Api/CustomerManagement/v13/CustomerManagementService.svc';

DataConnectionType=OAuth;

AuthUrl='https://login.microsoftonline.com/common/oauth2/v2.0/authorize';

TokenUrl='https://login.microsoftonline.com/common/oauth2/v2.0/token';

ReturnUrl='https://login.microsoftonline.com/common/oauth2/nativeclient';

RefreshTokenFilePath='c:\xxxxxx_some_folder_xxxxxxx\BingAds_RefreshToken.txt';

ScopeSeparator='{space}';

Scope='https://ads.microsoft.com/ads.manage offline_access';

ClientId='xxxxxxxxxxxxxx';

UseCustomApp=True;

RequestData='<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">

<s:Header>

<h:ApplicationToken i:nil="true" xmlns:h="https://bingads.microsoft.com/Customer/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />

<h:AuthenticationToken xmlns:h="https://bingads.microsoft.com/Customer/v13"><%access_token%></h:AuthenticationToken>

<h:DeveloperToken xmlns:h="https://bingads.microsoft.com/Customer/v13">1052Bxxxxxxxxxxxx</h:DeveloperToken>

<h:Password i:nil="true" xmlns:h="https://bingads.microsoft.com/Customer/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />

<h:UserName i:nil="true" xmlns:h="https://bingads.microsoft.com/Customer/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />

</s:Header>

<s:Body>

</GetUserRequest>

</s:Body>

</s:Envelope>';

RequestContentTypeCode=TextXml;

RequestMethod='POST';

RequestHeaders='SOAPAction: "GetUser"'

Next to OAuth Click on Configure Settings
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
Configure Redirect URI for Bing Ads API OAuth Connection
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)
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)
Click OK to save OAuth Connection UI to go back to main UI
You can now Click Test Connection to confirm everything
If you need to access Gateway from Other machine then go to Firewall tab and Click Add Rule
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.

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

Example-1

--use all default settings from gateway data source UI
--Read
Select * from OPENQUERY( [YourLinkedServer] , 'select * from $')&nbsp;

/*
--import
Select * into #tempTable from OPENQUERY( [YourLinkedServer] , 'select * from $') 
Select * from #tempTable
*/

--use all default settings from gateway data source UI

--Read

Select * from OPENQUERY( [YourLinkedServer] , 'select * from $') 

--import

Select * into #tempTable from OPENQUERY( [YourLinkedServer] , 'select * from $')

Select * from #tempTable

Example-2

--use Filter to extract specific Node from response XML
Select * from OPENQUERY( [YourLinkedServer] , 
'select * from $
WITH(Filter=''$.s:Envelope.s:Body.GetUserRequest[*]'', 
  ElementsToTreatAsArray=''GetUserResponse'' 
   )'
)

--use Filter to extract specific Node from response XML

Select * from OPENQUERY( [YourLinkedServer] ,

'select * from $

WITH(Filter=''$.s:Envelope.s:Body.GetUserRequest[*]'',

ElementsToTreatAsArray=''GetUserResponse''

)

Example-3

--Override URL, Headers and Body Settings. Change DeveloperToken in below request
--Header, Filter, URL, Request data is different for each Endpoint

SELECT * FROM OPENQUERY([YourLinkedServer]
, 'SELECT * FROM $
WITH(
	 ElementsToTreatAsArray=''GetUserResponse''
	,Src=''https://clientcenter.api.bingads.microsoft.com/Api/CustomerManagement/v13/CustomerManagementService.svc''
	,Filter=''$.s:Envelope.s:Body.GetUserResponse[*]''	
	,Header=''SOAPAction: "GetUser"''	
	,RequestData=''<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v13="https://bingads.microsoft.com/Customer/v13">
<soapenv:Header>
   <v13:DeveloperToken>1052xxxxxxxxxxxxx</v13:DeveloperToken>
   <v13:AuthenticationToken><%access_token%></v13:AuthenticationToken>
</soapenv:Header>
<soapenv:Body>
   <v13:GetUserRequest>
      <v13:UserId xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
   </v13:GetUserRequest>
</soapenv:Body>
</soapenv:Envelope>''
)')

--Override URL, Headers and Body Settings. Change DeveloperToken in below request

--Header, Filter, URL, Request data is different for each Endpoint

SELECT * FROM OPENQUERY([YourLinkedServer]

, 'SELECT * FROM $

WITH(

ElementsToTreatAsArray=''GetUserResponse''

,Src=''https://clientcenter.api.bingads.microsoft.com/Api/CustomerManagement/v13/CustomerManagementService.svc''

,Filter=''$.s:Envelope.s:Body.GetUserResponse[*]''

,Header=''SOAPAction: "GetUser"''

,RequestData=''<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v13="https://bingads.microsoft.com/Customer/v13">

<soapenv:Header>

<v13:DeveloperToken>1052xxxxxxxxxxxxx</v13:DeveloperToken>

<v13:AuthenticationToken><%access_token%></v13:AuthenticationToken>

</soapenv:Header>

<soapenv:Body>

<v13:GetUserRequest>

<v13:UserId xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>

</v13:GetUserRequest>

</soapenv:Body>

</soapenv:Envelope>''

)')

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)

Call SubmitGenerateReportResponse API to request new Report. This returns ReportRequestId
Now we have to keep checking every few seconds see report is ready or not. Call PollGenerateReport until Status field is Success from Pending
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)

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.
Visit https://ads.microsoft.com/cc/accounts
Obtain Bing Ads AccountId, ClientId for APi calls

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)
CustomerId
DeveloperToken

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Header>
    <h:ApplicationToken i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />
    <h:AuthenticationToken xmlns:h="https://bingads.microsoft.com/Reporting/v13"><%access_token%></h:AuthenticationToken>
    <h:CustomerAccountId xmlns:h="https://bingads.microsoft.com/Reporting/v13">481xxxxxxxxxxx</h:CustomerAccountId>
    <h:CustomerId xmlns:h="https://bingads.microsoft.com/Reporting/v13">191xxxxxxxxxxx</h:CustomerId>
    <h:DeveloperToken xmlns:h="https://bingads.microsoft.com/Reporting/v13">1052Bxxxxxxxxxxx</h:DeveloperToken>
    <h:Password i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />
    <h:UserName i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />
  </s:Header>
  <s:Body>
    <SubmitGenerateReportRequest xmlns="https://bingads.microsoft.com/Reporting/v13">
      <ReportRequest i:type="CampaignPerformanceReportRequest" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
        <ExcludeColumnHeaders>false</ExcludeColumnHeaders>
        <ExcludeReportFooter>false</ExcludeReportFooter>
        <ExcludeReportHeader>false</ExcludeReportHeader>
        <Format>Xml</Format>
        <ReportName>My Campaign Performance Report</ReportName>
        <ReturnOnlyCompleteData>false</ReturnOnlyCompleteData>
        <Aggregation>Hourly</Aggregation>
        <Columns>
          <CampaignPerformanceReportColumn>TimePeriod</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>AccountId</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>CampaignId</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>CampaignName</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>CampaignStatus</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>DeviceType</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>BidMatchType</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>QualityScore</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>AdRelevance</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>LandingPageExperience</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>Revenue</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>Assists</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>ExpectedCtr</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>DeliveredMatchType</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>AveragePosition</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>Conversions</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>AdDistribution</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>Network</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>Clicks</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>Impressions</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>Ctr</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>AverageCpc</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>Spend</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>LowQualityClicks</CampaignPerformanceReportColumn>
          <CampaignPerformanceReportColumn>LowQualityConversionRate</CampaignPerformanceReportColumn>
        </Columns>
        <Filter>
          <AccountStatus i:nil="true" />
          <AdDistribution i:nil="true" />
          <DeviceOS i:nil="true" />
          <DeviceType i:nil="true" />
          <Status i:nil="true" />
        </Filter>
        <Scope>
          <AccountIds xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
            <a:long>481xxxxxxxxxxx</a:long>
          </AccountIds>
          <Campaigns i:nil="true" />
        </Scope>
        <Time>
          <CustomDateRangeEnd i:nil="true" />
          <CustomDateRangeStart i:nil="true" />
          <PredefinedTime>Yesterday</PredefinedTime>
          <ReportTimeZone>PacificTimeUSCanadaTijuana</ReportTimeZone>
        </Time>
      </ReportRequest>
    </SubmitGenerateReportRequest>
  </s:Body>
</s:Envelope>

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">

<s:Header>

<h:ApplicationToken i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />

<h:AuthenticationToken xmlns:h="https://bingads.microsoft.com/Reporting/v13"><%access_token%></h:AuthenticationToken>

<h:CustomerAccountId xmlns:h="https://bingads.microsoft.com/Reporting/v13">481xxxxxxxxxxx</h:CustomerAccountId>

<h:CustomerId xmlns:h="https://bingads.microsoft.com/Reporting/v13">191xxxxxxxxxxx</h:CustomerId>

<h:DeveloperToken xmlns:h="https://bingads.microsoft.com/Reporting/v13">1052Bxxxxxxxxxxx</h:DeveloperToken>

<h:Password i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />

<h:UserName i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />

</s:Header>

<s:Body>

<ExcludeColumnHeaders>false</ExcludeColumnHeaders>

<ExcludeReportFooter>false</ExcludeReportFooter>

<ExcludeReportHeader>false</ExcludeReportHeader>

<ReportName>My Campaign Performance Report</ReportName>

<ReturnOnlyCompleteData>false</ReturnOnlyCompleteData>

<Aggregation>Hourly</Aggregation>

<CampaignPerformanceReportColumn>TimePeriod</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>AccountId</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>CampaignId</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>CampaignName</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>CampaignStatus</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>DeviceType</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>BidMatchType</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>QualityScore</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>AdRelevance</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>LandingPageExperience</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>Revenue</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>Assists</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>ExpectedCtr</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>DeliveredMatchType</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>AveragePosition</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>Conversions</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>AdDistribution</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>Network</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>Clicks</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>Impressions</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>AverageCpc</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>Spend</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>LowQualityClicks</CampaignPerformanceReportColumn>

<CampaignPerformanceReportColumn>LowQualityConversionRate</CampaignPerformanceReportColumn>

</Columns>

</Filter>

<Scope>

<a:long>481xxxxxxxxxxx</a:long>

</AccountIds>

</Scope>

<Time>

<PredefinedTime>Yesterday</PredefinedTime>

<ReportTimeZone>PacificTimeUSCanadaTijuana</ReportTimeZone>

</Time>

</ReportRequest>

</SubmitGenerateReportRequest>

</s:Body>

</s:Envelope>

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.

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Header>
    <h:ApplicationToken i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />
    <h:AuthenticationToken xmlns:h="https://bingads.microsoft.com/Reporting/v13"><%access_token%></h:AuthenticationToken>
    <h:CustomerAccountId xmlns:h="https://bingads.microsoft.com/Reporting/v13">480xxxxxxxxxxx</h:CustomerAccountId>
    <h:CustomerId xmlns:h="https://bingads.microsoft.com/Reporting/v13">191xxxxxxxxxxx</h:CustomerId>
    <h:DeveloperToken xmlns:h="https://bingads.microsoft.com/Reporting/v13">1052Bxxxxxxxxxxx</h:DeveloperToken>
    <h:Password i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />
    <h:UserName i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />
  </s:Header>
  <s:Body>
    <PollGenerateReportRequest xmlns="https://bingads.microsoft.com/Reporting/v13">
      <ReportRequestId>[$a.ReportRequestId$]</ReportRequestId>
    </PollGenerateReportRequest>
  </s:Body>
</s:Envelope>

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">

<s:Header>

<h:ApplicationToken i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />

<h:AuthenticationToken xmlns:h="https://bingads.microsoft.com/Reporting/v13"><%access_token%></h:AuthenticationToken>

<h:CustomerAccountId xmlns:h="https://bingads.microsoft.com/Reporting/v13">480xxxxxxxxxxx</h:CustomerAccountId>

<h:CustomerId xmlns:h="https://bingads.microsoft.com/Reporting/v13">191xxxxxxxxxxx</h:CustomerId>

<h:DeveloperToken xmlns:h="https://bingads.microsoft.com/Reporting/v13">1052Bxxxxxxxxxxx</h:DeveloperToken>

<h:Password i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />

<h:UserName i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />

</s:Header>

<s:Body>

<ReportRequestId>[$a.ReportRequestId$]</ReportRequestId>

</PollGenerateReportRequest>

</s:Body>

</s:Envelope>

Now try to execute below query. This should generate Performance report and download it in a SQL table for you.

select * into tmpBingAdsReport from OPENQUERY(Zs_BingAds,
'SELECT c.* FROM $
WITH(
	--submit report request for XML format

	 alias=''a'' 
	,Src=''https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc''
	,Filter=''$.s:Envelope.s:Body.SubmitGenerateReportResponse''
	,RequestData=''@C:\Requests\BingAds\sql_body1.xml''
	,IsMultiPart=''True''
	,RequestContentTypeCode=''TextXml''
	,Header=''SOAPAction: "SubmitGenerateReport"''
	,RequestMethod=''POST''
	,EnableBodyPlaceholderForMultiPart=''True''
	,Meta=''[{"Name": "ReportRequestId","Type": "Int64"}]''

	--keep checking status until report is ready - returns final url when done

	,join1_alias=''b''
	,join1_Filter=''$.s:Envelope.s:Body.PollGenerateReportResponse.ReportRequestStatus''
	,join1_RequestData=''@C:\Requests\BingAds\sql_body2.xml''
	,join1_IsMultiPart=''True''
	,join1_Src=''https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc''
	,join1_RequestContentTypeCode=''TextXml''
	,join1_Header=''SOAPAction: "PollGenerateReport"''
	,join1_RequestMethod=''POST''
	,join1_EnableBodyPlaceholderForMultiPart=''True''
	,join1_EnableStatusCheck=''True'', 
	,join1_StatucCheckMaxWaitSeconds=300, --wait max 5 mins?
	,join1_StatucCheckIterationWaitSeconds=5, --check every 5 sec?
	,join1_StatusSuccessValue=''Success'', --look for success word in response

	--Download report (Zip file) and Parse rows

	,join2_alias=''c''
	,join2_DataConnectionType=''Default''
	,join2_Src=''[$b.ReportDownloadUrl$]''
	,join2_Filter=''$.Report.Table.Row[*]''
	,join2_ElementsToTreatAsArray=''Row''	
	,join2_RequestMethod=''GET''
	,join2_IsMultiPart=''False''
	,join2_FileCompressionType=''Zip''	
)')

select * from tmpBingAdsReport

select * into tmpBingAdsReport from OPENQUERY(Zs_BingAds,

'SELECT c.* FROM $

WITH(

--submit report request for XML format

alias=''a''

,Src=''https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc''

,Filter=''$.s:Envelope.s:Body.SubmitGenerateReportResponse''

,RequestData=''@C:\Requests\BingAds\sql_body1.xml''

,IsMultiPart=''True''

,RequestContentTypeCode=''TextXml''

,Header=''SOAPAction: "SubmitGenerateReport"''

,RequestMethod=''POST''

,EnableBodyPlaceholderForMultiPart=''True''

,Meta=''[{"Name": "ReportRequestId","Type": "Int64"}]''

--keep checking status until report is ready - returns final url when done

,join1_alias=''b''

,join1_Filter=''$.s:Envelope.s:Body.PollGenerateReportResponse.ReportRequestStatus''

,join1_RequestData=''@C:\Requests\BingAds\sql_body2.xml''

,join1_IsMultiPart=''True''

,join1_Src=''https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc''

,join1_RequestContentTypeCode=''TextXml''

,join1_Header=''SOAPAction: "PollGenerateReport"''

,join1_RequestMethod=''POST''

,join1_EnableBodyPlaceholderForMultiPart=''True''

,join1_EnableStatusCheck=''True'',

,join1_StatucCheckMaxWaitSeconds=300, --wait max 5 mins?

,join1_StatucCheckIterationWaitSeconds=5, --check every 5 sec?

,join1_StatusSuccessValue=''Success'', --look for success word in response

--Download report (Zip file) and Parse rows

,join2_alias=''c''

,join2_DataConnectionType=''Default''

,join2_Src=''[$b.ReportDownloadUrl$]''

,join2_Filter=''$.Report.Table.Row[*]''

,join2_ElementsToTreatAsArray=''Row''

,join2_RequestMethod=''GET''

,join2_IsMultiPart=''False''

,join2_FileCompressionType=''Zip''

)')

select * from tmpBingAdsReport

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.

/*
RPC and RPC OUT options must be turned on to run this proc

EXEC master.dbo.sp_serveroption @server=N'Zs_BingAds', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'Zs_BingAds', @optname=N'rpc out', @optvalue=N'true'
--Below needed to support EXEC + INSERT (dynamic query)
EXEC master.dbo.sp_serveroption @server=N'Zs_BingAds, @optname=N'remote proc transaction promotion', @optvalue=N'false'
Also 
1. Change Body file path
2. Change CustomerAccountId, CustomerId, DeveloperToken
*/

ALTER proc usp_Api_BingAds_Reports
as
--///////////////////////////////////////////////////////////////////////////////
--Step-1 - submit report request for XML format
--///////////////////////////////////////////////////////////////////////////////
create table #tmpBingAdsReport_Step1(ReportRequestId bigint)
INSERT into #tmpBingAdsReport_Step1
EXEC(
'SELECT * FROM $
WITH(
 Src=''https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc''
,Filter=''$.s:Envelope.s:Body.SubmitGenerateReportResponse''
,RequestData=''@c:\BWProjects\Requests\BingAds\sql_body1.xml''
,IsMultiPart=''True''
,RequestContentTypeCode=''TextXml''
,Header=''SOAPAction: "SubmitGenerateReport"''
,RequestMethod=''POST''
,EnableBodyPlaceholderForMultiPart=''True''
,Meta=''[{"Name": "ReportRequestId","Type": "Int64"}]''
)') AT zs_BingAds


--///////////////////////////////////////////////////////////////////////////////
--Step2 - keep checking status until report is ready - returns final url when done
--///////////////////////////////////////////////////////////////////////////////

declare @reportid varchar(100)
select top 1 @reportid=cast(ReportRequestId as varchar(100)) from #tmpBingAdsReport_Step1

create table #tmpBingAdsReport_Step2(ReportDownloadUrl varchar(500))
INSERT into #tmpBingAdsReport_Step2 
EXEC(
'SELECT * FROM $
WITH(
 Filter=''$.s:Envelope.s:Body.PollGenerateReportResponse.ReportRequestStatus''
,RequestData=''<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Header>
    <h:ApplicationToken i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />
    <h:AuthenticationToken xmlns:h="https://bingads.microsoft.com/Reporting/v13"><%access_token%></h:AuthenticationToken>
    <h:CustomerAccountId xmlns:h="https://bingads.microsoft.com/Reporting/v13">480xxxxxxxxx</h:CustomerAccountId>
    <h:CustomerId xmlns:h="https://bingads.microsoft.com/Reporting/v13">191xxxxxxxxx</h:CustomerId>
    <h:DeveloperToken xmlns:h="https://bingads.microsoft.com/Reporting/v13">105xxxxxxxxxxx</h:DeveloperToken>
    <h:Password i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />
    <h:UserName i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />
  </s:Header>
  <s:Body>
    <PollGenerateReportRequest xmlns="https://bingads.microsoft.com/Reporting/v13">
      <ReportRequestId>'+ @reportid +'</ReportRequestId>
    </PollGenerateReportRequest>
  </s:Body>
</s:Envelope>''
--,IsMultiPart=''True''
,Src=''https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc''
,RequestContentTypeCode=''TextXml''
,Header=''SOAPAction: "PollGenerateReport"''
,RequestMethod=''POST''
--,EnableBodyPlaceholderForMultiPart=''True''
,EnableStatusCheck=''True'', 
,StatucCheckMaxWaitSeconds=300, --wait max 5 mins?
,StatucCheckIterationWaitSeconds=5, --check every 5 sec?
,StatusSuccessValue=''Success'', --look for success word in response
,Meta=''[{"Name": "ReportDownloadUrl","Type": "String",Length:"300"}]''
)') AT zs_BingAds


declare @reportpath varchar(500)
select top 1 @reportpath=ReportDownloadUrl from #tmpBingAdsReport_Step2

select * from #tmpBingAdsReport_Step1
select * from #tmpBingAdsReport_Step2

--///////////////////////////////////////////////////////////////////////////////
--Step-3 - Get report file
--///////////////////////////////////////////////////////////////////////////////
--Static table
--//INSERT INTO BingAdsReport
EXEC(
'SELECT * FROM $
WITH(
 DataConnectionType=''Default''
,Src='''+ @reportpath +'''
,Filter=''$.Report.Table.Row[*]''
,ElementsToTreatAsArray=''Row''	
,RequestMethod=''GET''
,IsMultiPart=''False''
,FileCompressionType=''Zip''	
)') AT zs_BingAds


--select * from tmpBingAdsReport_Step3
--select * from openquery(Bing, 'select * from $')


/*
select * into tmpBingAdsReport from OPENQUERY(zs_BingAds,
'SELECT c.* FROM $
WITH(
--submit report request for XML format

alias=''a'' 
,Src=''https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc''
,Filter=''$.s:Envelope.s:Body.SubmitGenerateReportResponse''
,RequestData=''@c:\BWProjects\Requests\BingAds\sql_body1.xml''
,IsMultiPart=''True''
,RequestContentTypeCode=''TextXml''
,Header=''SOAPAction: "SubmitGenerateReport"''
,RequestMethod=''POST''
,EnableBodyPlaceholderForMultiPart=''True''
,Meta=''[{"Name": "ReportRequestId","Type": "Int64"}]''

--keep checking status until report is ready - returns final url when done

,join1_alias=''b''
,join1_Filter=''$.s:Envelope.s:Body.PollGenerateReportResponse.ReportRequestStatus''
,join1_RequestData=''@c:\BWProjects\Requests\BingAds\sql_body2.xml''
,join1_IsMultiPart=''True''
,join1_Src=''https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc''
,join1_RequestContentTypeCode=''TextXml''
,join1_Header=''SOAPAction: "PollGenerateReport"''
,join1_RequestMethod=''POST''
,join1_EnableBodyPlaceholderForMultiPart=''True''
,join1_EnableStatusCheck=''True'', 
,join1_StatucCheckMaxWaitSeconds=300, --wait max 5 mins?
,join1_StatucCheckIterationWaitSeconds=5, --check every 5 sec?
,join1_StatusSuccessValue=''Success'', --look for success word in response

--Download report (Zip file) and Parse rows

,join2_alias=''c''
,join2_DataConnectionType=''Default''
,join2_Src=''[$b.ReportDownloadUrl$]''
,join2_Filter=''$.Report.Table.Row[*]''
,join2_ElementsToTreatAsArray=''Row''	
,join2_RequestMethod=''GET''
,join2_IsMultiPart=''False''
,join2_FileCompressionType=''Zip''	
)')

select * from tmpBingAdsReport

select * from openquery(Bing, 'select * from $')
*/
go

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

RPC and RPC OUT options must be turned on to run this proc

EXEC master.dbo.sp_serveroption @server=N'Zs_BingAds', @optname=N'rpc', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'Zs_BingAds', @optname=N'rpc out', @optvalue=N'true'

--Below needed to support EXEC + INSERT (dynamic query)

EXEC master.dbo.sp_serveroption @server=N'Zs_BingAds, @optname=N'remote proc transaction promotion', @optvalue=N'false'

Also

1. Change Body file path

2. Change CustomerAccountId, CustomerId, DeveloperToken

ALTER proc usp_Api_BingAds_Reports

--///////////////////////////////////////////////////////////////////////////////

--Step-1 - submit report request for XML format

--///////////////////////////////////////////////////////////////////////////////

create table #tmpBingAdsReport_Step1(ReportRequestId bigint)

INSERT into #tmpBingAdsReport_Step1

EXEC(

'SELECT * FROM $

WITH(

Src=''https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc''

,Filter=''$.s:Envelope.s:Body.SubmitGenerateReportResponse''

,RequestData=''@c:\BWProjects\Requests\BingAds\sql_body1.xml''

,IsMultiPart=''True''

,RequestContentTypeCode=''TextXml''

,Header=''SOAPAction: "SubmitGenerateReport"''

,RequestMethod=''POST''

,EnableBodyPlaceholderForMultiPart=''True''

,Meta=''[{"Name": "ReportRequestId","Type": "Int64"}]''

)') AT zs_BingAds

--///////////////////////////////////////////////////////////////////////////////

--Step2 - keep checking status until report is ready - returns final url when done

--///////////////////////////////////////////////////////////////////////////////

declare @reportid varchar(100)

select top 1 @reportid=cast(ReportRequestId as varchar(100)) from #tmpBingAdsReport_Step1

create table #tmpBingAdsReport_Step2(ReportDownloadUrl varchar(500))

INSERT into #tmpBingAdsReport_Step2

EXEC(

'SELECT * FROM $

WITH(

Filter=''$.s:Envelope.s:Body.PollGenerateReportResponse.ReportRequestStatus''

,RequestData=''<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">

<s:Header>

<h:ApplicationToken i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />

<h:AuthenticationToken xmlns:h="https://bingads.microsoft.com/Reporting/v13"><%access_token%></h:AuthenticationToken>

<h:CustomerAccountId xmlns:h="https://bingads.microsoft.com/Reporting/v13">480xxxxxxxxx</h:CustomerAccountId>

<h:CustomerId xmlns:h="https://bingads.microsoft.com/Reporting/v13">191xxxxxxxxx</h:CustomerId>

<h:DeveloperToken xmlns:h="https://bingads.microsoft.com/Reporting/v13">105xxxxxxxxxxx</h:DeveloperToken>

<h:Password i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />

<h:UserName i:nil="true" xmlns:h="https://bingads.microsoft.com/Reporting/v13" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" />

</s:Header>

<s:Body>

<ReportRequestId>'+ @reportid +'</ReportRequestId>

</PollGenerateReportRequest>

</s:Body>

</s:Envelope>''

--,IsMultiPart=''True''

,Src=''https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc''

,RequestContentTypeCode=''TextXml''

,Header=''SOAPAction: "PollGenerateReport"''

,RequestMethod=''POST''

--,EnableBodyPlaceholderForMultiPart=''True''

,EnableStatusCheck=''True'',

,StatucCheckMaxWaitSeconds=300, --wait max 5 mins?

,StatucCheckIterationWaitSeconds=5, --check every 5 sec?

,StatusSuccessValue=''Success'', --look for success word in response

,Meta=''[{"Name": "ReportDownloadUrl","Type": "String",Length:"300"}]''

)') AT zs_BingAds

declare @reportpath varchar(500)

select top 1 @reportpath=ReportDownloadUrl from #tmpBingAdsReport_Step2

select * from #tmpBingAdsReport_Step1

select * from #tmpBingAdsReport_Step2

--///////////////////////////////////////////////////////////////////////////////

--Step-3 - Get report file

--///////////////////////////////////////////////////////////////////////////////

--Static table

--//INSERT INTO BingAdsReport

EXEC(

'SELECT * FROM $

WITH(

DataConnectionType=''Default''

,Src='''+ @reportpath +'''

,Filter=''$.Report.Table.Row[*]''

,ElementsToTreatAsArray=''Row''

,RequestMethod=''GET''

,IsMultiPart=''False''

,FileCompressionType=''Zip''

)') AT zs_BingAds

--select * from tmpBingAdsReport_Step3

--select * from openquery(Bing, 'select * from $')

select * into tmpBingAdsReport from OPENQUERY(zs_BingAds,

'SELECT c.* FROM $

WITH(

--submit report request for XML format

alias=''a''

,Src=''https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc''

,Filter=''$.s:Envelope.s:Body.SubmitGenerateReportResponse''

,RequestData=''@c:\BWProjects\Requests\BingAds\sql_body1.xml''

,IsMultiPart=''True''

,RequestContentTypeCode=''TextXml''

,Header=''SOAPAction: "SubmitGenerateReport"''

,RequestMethod=''POST''

,EnableBodyPlaceholderForMultiPart=''True''

,Meta=''[{"Name": "ReportRequestId","Type": "Int64"}]''

--keep checking status until report is ready - returns final url when done

,join1_alias=''b''

,join1_Filter=''$.s:Envelope.s:Body.PollGenerateReportResponse.ReportRequestStatus''

,join1_RequestData=''@c:\BWProjects\Requests\BingAds\sql_body2.xml''

,join1_IsMultiPart=''True''

,join1_Src=''https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc''

,join1_RequestContentTypeCode=''TextXml''

,join1_Header=''SOAPAction: "PollGenerateReport"''

,join1_RequestMethod=''POST''

,join1_EnableBodyPlaceholderForMultiPart=''True''

,join1_EnableStatusCheck=''True'',

,join1_StatucCheckMaxWaitSeconds=300, --wait max 5 mins?

,join1_StatucCheckIterationWaitSeconds=5, --check every 5 sec?

,join1_StatusSuccessValue=''Success'', --look for success word in response

--Download report (Zip file) and Parse rows

,join2_alias=''c''

,join2_DataConnectionType=''Default''

,join2_Src=''[$b.ReportDownloadUrl$]''

,join2_Filter=''$.Report.Table.Row[*]''

,join2_ElementsToTreatAsArray=''Row''

,join2_RequestMethod=''GET''

,join2_IsMultiPart=''False''

,join2_FileCompressionType=''Zip''

)')

select * from tmpBingAdsReport

select * from openquery(Bing, 'select * from $')

Conclusion

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.