{"id":1000,"date":"2017-02-14T19:28:31","date_gmt":"2017-02-14T19:28:31","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=1000"},"modified":"2017-10-09T13:48:23","modified_gmt":"2017-10-09T13:48:23","slug":"calling-google-dfp-api-with-ssis-doubleclick-soap-api","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/calling-google-dfp-api-with-ssis-doubleclick-soap-api\/","title":{"rendered":"How to call Google DFP API with SSIS – DoubleClick"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>Google DoubleClick is one of the most popular platforms for Advertisers. Many times you have need for custom integration or Automation for many operations. In this article we will learn how to Call Google DFP API (i.e. DoubleClick for Publishers) without coding using SSIS (Microsoft SQL Server Integration Services). In our previous article we discussed how to Integrate Google AdWords API using SSIS (Click here)<\/a>.<\/p>\n

If you are new to SSIS then no worry there are plenty of tutorials for ssis<\/a><\/p>\n

To achieve Custom integration for DoubleClick (DFP) API we will use SSIS XML Source<\/a>. XML Source is part of SSIS PowerPack<\/a> which has 45+ connectors and Tasks.<\/p>\n

 <\/p>\n

Prerequisite<\/h2>\n

Before you can finish tasks explained in this article you have to finish below steps. This article assumes you have basic knowledge of SSIS (SQL Server Integration Services)<\/p>\n

    \n
  1. Make sure you SSIS designer installed. Sometimes its referred as BIDS or SSDT (Get from here)<\/a><\/li>\n
  2. Download SSIS PowerPack<\/a><\/li>\n
  3. Download SoapUI<\/a> (Its free third party tool to test SOAP API). SoapUI can help you to generate XML Request BODY easily from WSDL file provided by API Vendor. You can also test your service by supplying parameters.<\/li>\n
  4. Optional – Another very useful Free tool is Fiddler. You can use it to see raw request\/response (Check this article<\/a>)<\/li>\n<\/ol>\n

    Download DFP API Example SSIS Package<\/h2>\n

    Click here to DFP API download Sample for SSIS<\/a> SSIS Package (SSIS 2012, 2014, 2016)<\/p>\n

    Screenshot of Sample Package:<\/p>\n

    \"Example<\/a>

    Example SSIS Package – Google DFP API Integration (Read \/ Write data in Google Double Click for Publisher) – No Coding required<\/p><\/div>\n

    Making your first DFP API Call using SSIS<\/h2>\n

    Once you install PowerPack<\/a> you are ready to execute your very first Google DFP API Call.<\/p>\n

    NOTE<\/strong>: If you don’t have any DFP network for test then see next section (Explains how to create test network for testing).<\/p>\n

    Lets look at how to call Google DFP API step-by-step.<\/p>\n

      \n
    1. Create new SSIS Project and open package designer<\/li>\n
    2. Drag ZS REST API Task<\/a> from SSIS control flow toolbox and drop it on designer.
      \n
      REST API Task<\/a> is useful when you want to call SOAP\/REST API but not necessarily parse response into rows and columns. We will look at JSON Source<\/a> later in this article which can actually parse response into rows and columns (Useful to load Google DFP data into SQL Server or other RDBMS \/ flatfile )<\/div><\/div><\/li>\n
    3. Double click REST API Task to configure. Select Access mode to [Url from Connection]<\/li>\n
    4. Enter following URL in the URL Textbox\n
      \u00a0https:\/\/ads.google.com\/apis\/ads\/publisher\/v201702\/NetworkService<\/pre>\n<\/li>\n
    5. From the connection dialogbox select ZS-OAUTH<\/strong> to create new OAuth Connection for DFP API<\/li>\n
    6. On OAuth Connection dialogbox select Google<\/strong> from Provider dropdown. In this demo we will use Default OAuth App but you can register your own google OAuth app<\/a> if you wish to use Custom OAuth option from UI.<\/li>\n
    7. Enter following Scopes in the Scopes textbox (or select manually by clicking Select Scopes button). Scope is nothing but permission for API (In our case View\/Manage DFP data and Read\/Write Report Files to Cloud Storage).\n
      https:\/\/www.googleapis.com\/auth\/dfp\r\nhttps:\/\/www.googleapis.com\/auth\/devstorage.read_only\r\nhttps:\/\/www.googleapis.com\/auth\/devstorage.write_only\r\nhttps:\/\/www.googleapis.com\/auth\/cloud-platform.read-only\r\nhttps:\/\/www.googleapis.com\/auth\/devstorage.full_control\r\nhttps:\/\/www.googleapis.com\/auth\/cloud-platform\r\nhttps:\/\/www.googleapis.com\/auth\/devstorage.read_write<\/pre>\n<\/li>\n
    8. If you are not going call ReportService<\/a> API to generate Reports in CSV format then you will need only\u00a0one scope\n
      https:\/\/www.googleapis.com\/auth\/dfp<\/pre>\n<\/li>\n
    9. Click Generate Token button. You will see browser popup for login and then Accept option to confirm permissions like below. Once you don’t it will populate tokens and prompt to save tokens to backup file. Secure token backup\u00a0to safe place.\n
    10. Once done Click Test Connection and if its green then Click OK to save connection<\/li>\n
    11. Once you are back to REST API Task UI then select Method to POST<\/strong><\/li>\n
    12. Select Request Body\u00a0Content Type<\/strong> to XML (i.e. text\/xml)<\/li>\n
    13. Click edit button next to Request Body<\/strong> and enter following XML.\n
      <soapenv:Envelope xmlns:soapenv=\"http:\/\/schemas.xmlsoap.org\/soap\/envelope\/\" xmlns:v20=\"https:\/\/www.google.com\/apis\/ads\/publisher\/v201702\">\r\n   <soapenv:Header>\r\n      <v20:RequestHeader\/>\r\n   <\/soapenv:Header>\r\n   <soapenv:Body>\r\n      <v20:getAllNetworks\/>\r\n   <\/soapenv:Body>\r\n<\/soapenv:Envelope><\/pre>\n
      \n
      If you are wondering how did we get above XML fragment then read next section about using 3rd party tool called SoapUI. Also we have detail article for calling SOAP request.\u00a0Check\u00a0this article on how to use SoapUI (free 3rd party tool) to create SOAP request Bodyfrom WSDL<\/a>.\u00a0DFP has many API endpoints for different actions (e.g. NetworkService<\/a>\u00a0,\u00a0InventoryService<\/a>\u00a0, …). For each service you have different Api URL and different WSDL. In the next section you can learn how to create correct SOAP Body (for POST) using SoapUI<\/strong> tool.<\/p>\n
      For example if you using DFP NetworkService API for version v201702 then your help page URL would be like this<\/strong>
      \n      https:\/\/developers.google.com\/doubleclick-publishers\/docs\/reference\/v201702\/NetworkService<\/a>
      \nAnd on the same page you will see WSDL link below<\/strong>
      \n      https:\/\/adwords.google.com\/api\/adwords\/mcm\/v201609\/ManagedCustomerService?wsdl<\/a>
      \n(Just download that WSDL XML and save to local disk then use with SoapUI to generate XML body to submit for any DFP API call.)<\/p>\n<\/div><\/div><\/li>\n
    14. Once you done with above steps, your Task Configuration will look like below.\n
    15. Now click Test Request button. You will see Response popup with below content if its successful (Scroll at the bottom on response\u00a0form and you will see XML data) .. see below\n
      ------------------------------------\r\nRequest\r\n------------------------------------\r\nPOST https 1.1 ==> \/apis\/ads\/publisher\/v201702\/NetworkService\r\nHost: ads.google.com\r\n\r\n>>>> HEADERS <<<<<\r\nAuthorization: Bearer ya29.Glz-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\r\nContent-Type: text\/xml;charset=UTF-8\r\nUser-Agent: ZappySysApp\r\nHost: ads.google.com\r\nContent-Length: 291\r\nExpect: 100-continue\r\nAccept-Encoding: gzip, deflate\r\nConnection: Keep-Alive\r\n\r\n------------------------------------\r\nResponse\r\n------------------------------------\r\nPOST 1.1 200 OK==> OK\r\n\r\n>>>> HEADERS <<<<<\r\nContent-Encoding: \r\nX-Content-Type-Options: nosniff\r\nX-Frame-Options: SAMEORIGIN\r\nX-XSS-Protection: 1; mode=block\r\nAlt-Svc: quic=\":443\"; ma=2592000; v=\"35,34\"\r\nTransfer-Encoding: chunked\r\nCache-Control: private, max-age=0\r\nContent-Type: text\/xml; charset=UTF-8\r\nDate: Sun, 26 Feb 2017 01:18:32 GMT\r\nExpires: Sun, 26 Feb 2017 01:18:32 GMT\r\nServer: GSE\r\n\r\n>>>> Cookies <<<<<\r\n\r\n>>>> CONTENT <<<<<\r\n\r\n<soap:Envelope xmlns:soap=\"http:\/\/schemas.xmlsoap.org\/soap\/envelope\/\">\r\n\t<soap:Header>\r\n\t\t<ResponseHeader xmlns=\"https:\/\/www.google.com\/apis\/ads\/publisher\/v201702\">\r\n\t\t\t<requestId>27ab3d2e4e1fcb8f444b317d0b0f871d<\/requestId>\r\n\t\t\t<responseTime>50<\/responseTime>\r\n\t\t<\/ResponseHeader>\r\n\t<\/soap:Header>\r\n\t<soap:Body>\r\n\t\t<getAllNetworksResponse xmlns=\"https:\/\/www.google.com\/apis\/ads\/publisher\/v201702\">\r\n\t\t\t<rval>\r\n\t\t\t\t<id>551196<\/id>\r\n\t\t\t\t<displayName>XFP sandbox property<\/displayName>\r\n\t\t\t\t<networkCode>238897396<\/networkCode>\r\n\t\t\t\t<propertyCode>ca-pub-2799179143725683<\/propertyCode>\r\n\t\t\t\t<timeZone>America\/New_York<\/timeZone>\r\n\t\t\t\t<currencyCode>USD<\/currencyCode>\r\n\t\t\t\t<effectiveRootAdUnitId>237897516<\/effectiveRootAdUnitId>\r\n\t\t\t\t<isTest>true<\/isTest>\r\n\t\t\t<\/rval>\r\n\t\t<\/getAllNetworksResponse>\r\n\t<\/soap:Body>\r\n<\/soap:Envelope>\r\n<\/pre>\n
       <\/li>\n<\/ol>\n
      How to generate SOAP Request Body using SoapUI tool<\/h2>\n
      This section describes how to create DoubleClick SOAP API Request Body (XML fragment) for any DFP SOAP API call described anywhere in this article.<\/p>\n
        \n
      1. Download SoapUI<\/a> (Its free third party tool to test SOAP API). SoapUI can help you to generate XML Request BODY easily from WSDL file provided by API Vendor. You can also test your service by supplying parameters.<\/li>\n
      2. Now download WDSL xml for appropriate API calls you want to make.\u00a0Assume that you want to call \u00a0getAllNetworksResponse<\/strong> API found under\u00a0NetworkService<\/a>. For that first navigate to help file page and open help page. Make sure you select correct version from help navigation. Copy WSDL link and open in new browser window. Save XML to local disk (e.g. c:\\api\\dfp_networkservice_wsdl.xml\u00a0(See below screenshot how to find DFP API WSDL link)\n
      3. Once SoapUI is downloaded and installed click File<\/strong> menu > Create New SOAP Project<\/strong> option<\/li>\n
      4. Name your project (e.g. DFP API) and specify WSDL URL or File Path if it was saved locally (e.g. c:\\api\\dfp_networkservice_wsdl.xml) and click OK<\/li>\n
      5. Now navigate to API\u00a0Action for which you would like to get Body. Click Request node (If missing create new) and edit request. You may see XML like below (If some optional parameters not visible in XML then click Re-create request with optional parameters button from toolbar)\n
      6. Edit necessary parameters from above XML code and copy to Body of SSIS REST API Task.<\/li>\n<\/ol>\n
        Creating test network – DFP Sandbox<\/h2>\n
        Very first step we recommend before testing DFP API call is create test network<\/a>. You can call DFP API against LIVE network but not recommended if you are calling create\/update API.<\/p>\n
        To create test network you can follow exact same steps described in the previous section except the Body step (Step#12). Use following Request body to call makeTestNetwork command.<\/p>\n
        API makeTestNetwork –\u00a0Request<\/h3>\n
        URL:\u00a0https:\/\/ads.google.com\/apis\/ads\/publisher\/v201702\/NetworkService
        \nBody (see below):<\/p>\n
        <soapenv:Envelope xmlns:soapenv=\"http:\/\/schemas.xmlsoap.org\/soap\/envelope\/\" xmlns:v20=\"https:\/\/www.google.com\/apis\/ads\/publisher\/v201702\">\r\n   <soapenv:Header>\r\n      <v20:RequestHeader\/>\r\n   <\/soapenv:Header>\r\n   <soapenv:Body>\r\n      <v20:makeTestNetwork\/>\r\n   <\/soapenv:Body>\r\n<\/soapenv:Envelope><\/pre>\n
        Once you click Test you may receive following response. Note your network code (e.g. 1122334455\u00a0from below). This network code is used in pretty much all DFP API to view or manage ad network related items or properties.<\/p>\n
        API makeTestNetwork – Response<\/h3>\n
        <soap:Envelope xmlns:soap=\"http:\/\/schemas.xmlsoap.org\/soap\/envelope\/\">\r\n\t<soap:Header>\r\n\t\t<ResponseHeader xmlns=\"https:\/\/www.google.com\/apis\/ads\/publisher\/v201702\">\r\n\t\t\t<requestId>ba61d79efee325bf4b5aa45c46836021<\/requestId>\r\n\t\t\t<responseTime>1021<\/responseTime>\r\n\t\t<\/ResponseHeader>\r\n\t<\/soap:Header>\r\n\t<soap:Body>\r\n\t\t<makeTestNetworkResponse xmlns=\"https:\/\/www.google.com\/apis\/ads\/publisher\/v201702\">\r\n\t\t\t<rval>\r\n\t\t\t\t<id>551196<\/id>\r\n\t\t\t\t<displayName>XFP sandbox property<\/displayName>\r\n\t\t\t\t<networkCode>1122334455<\/networkCode>\r\n\t\t\t\t<propertyCode>ca-pub-2799179143725683<\/propertyCode>\r\n\t\t\t\t<timeZone>America\/Los_Angeles<\/timeZone>\r\n\t\t\t\t<currencyCode>USD<\/currencyCode>\r\n\t\t\t\t<effectiveRootAdUnitId>237897516<\/effectiveRootAdUnitId>\r\n\t\t\t\t<isTest>true<\/isTest>\r\n\t\t\t<\/rval>\r\n\t\t<\/makeTestNetworkResponse>\r\n\t<\/soap:Body>\r\n<\/soap:Envelope><\/pre>\n
        Above response contains test networkcode copy that code becuase you will need in almost every API call you make later. You can only have one test network. If test network is already created and you try to execute above code then you may receive error \u00a0AuthenticationError.GOOGLE_ACCOUNT_ALREADY_ASSOCIATED_WITH_NETWORK<\/strong><\/p>\n
        You can also get network code using two different ways.<\/p>\n