Get data from Facebook in SSIS using REST API

Introduction

In this article you will learn how to get data from Facebook using Graph API (OAuth 2.0) using SSIS PowerPack REST api Connectors. Facebook provides rich set of REST API to access data. Their REST API is also known as Facebook Graph API and it uses OAuth 2.0 Authorization. Traditionally any REST API Integration scenarios in SSIS requires you to use some sort of SDKs using popular programming languages (e.g. C#, Java, Ruby, Python). If you are DBA or SSIS Developer and you prefer to use clean drag and drop approach rather complex programming then this article will show you how to achieve it.

SSIS PowerPack REST API Connectors can be used to consume/manage Facebook data using REST API. Following connectors will be used in this article.

Custom SSIS Tasks - Call REST API Webservice (GET, POST, DELETE etc) REST API Task
SSIS OAuth Connection Manager OAuth Connection Manager
Custom SSIS Components - JSON Source (File, REST, OData) JSON Source (File, REST, OData)

Register Facebook OAuth App for Graph API access

Very first step to access Facebook API is register your custom OAuth App under Facebook developer portal. Check this article for more information on this topic.

Consume data from Facebook using REST API in SSIS

In this section we will guide you how to create OAuth connection and consume data from Facebook in few clicks using JSON Source (File, REST, OData) . We will load Facebook data into SQL Server.

Create Facebook OAuth Connection

You can create SSIS OAuth connection manager two ways. 1) From Add Connection manager option from designer or Click Add ZS-OAuth from Component UI. Lets use first method.

  1. In the SSIS Designer in connection manager pane .. right click and new connection … select ZS-OAUTH Connection from list
  2. Select Facebook provider from dropdown
  3. Select Custom OAuth App option
  4. Enter Facebook OAuth App ID and OAuth App Secret
  5. Select Permission (i.e. Scope) or type by hand (1 per each line)
  6. Click generate Token. You will be redirected to login page and then it will show Authorize App confirmation. Click Accept.
  7. Test Connection (If things go well it will show green success)
    Testing Facebook OAuth Connectivity in SSIS (Use Graph API)

    Testing Facebook OAuth Connectivity in SSIS (Use Graph API)

Use JSON/REST API Source to read from Graph API

Once you have OAuth connection manager. You can perform following steps to Extract data from Graph API Url. Facebook Graph API uses ObjectID in the URL so make sure you have that handy for whatever resource you want to access.

Find Facebook PageID from its Name

In our case for example we want to Extract Page Insights for some page (e.g. facebook.com/MyPage ) . To findout pageid perform following steps

  1. Navigate to https://developers.facebook.com/tools/explorer
  2. Click Get Token button, select permission you really care (To get PageID you don’t need to check anything).
  3. Type /YourPageName in the API Url bar
  4. Now click Submit. You will see response json which will have pagename and pageID
    Using Facebook Graph API Explorer, Test API, Find Object ID / PageID from Name

    Using Facebook Graph API Explorer, Test API, Find Object ID / PageID from Name

 

Configure JSON/REST API Source

Once you know pageid we can build our API URL. Lets assume your pageid is 12345678 and you want to extract page insight data via API call. In that case you may build your URL following way. You can change v2.5 to something else depending on API version you wish to use. If you omit version number from URL then default version will be used by Facebook API (Default API version can be found on Dashboard of your OAuth App).

Now perform following steps to configure JSON Source to extract data from Facebook API.

NOTE: Below steps assume that you already Downloaded and Installed SSIS PowerPack

  1. Drag Data Flow Task from SSIS Toolbox and double click to view Dataflow designer
  2. Drag ZS JSON Source from SSIS Toolbox. Double click to configure.
  3. In the AccessMode select “File Path or Web URL”
  4. Enter URL ( e.g. https://graph.facebook.com/v2.5/12345678/insights/page_impressions_organic_unique ).
    NOTE: You may use variables in the URL e.g. http://mydomain/{{User::MyPath}}
  5. Check use credentials option and select existing or create new OAuth Connection manager
  6. Click on Select Filter and select values node and click OK. It may produce filter expression like below.
    Facebook REST API (Filter Data using Json Path Expression)

    Facebook REST API (Filter Data using Json Path Expression)

  7. You can set Max Rows option to desired value if you don’t want to extract all rows (0=Unlimited)
  8. Goto Paging Tab and enter $.paging.next in the “Next Cursor” field as below
    Facebook REST API Pagination (Loop through large data set)

    Facebook REST API Pagination (Loop through large data set)

  9. Click on Columns tab if you wish to see/change metadata (You can change size / datatype there and Lock metadata to prevent auto refresh)
  10. Now click Preview to see sample data and then exit preview and click OK to save UI changes
    Extract data from Facebook using Graph API (SSIS JSON/REST API Connector) - Load into SQL Server Table

    Extract data from Facebook using Graph API (SSIS JSON/REST API Connector) – Load into SQL Server Table

Load Facebook data into SQL Server

If you wish to save Facebook data into SQL Server or any other target like Oracel, MySQL, FlatFile, Excel etc then you can use some native SSIS Connectors (e.g. OLEDB Destination, ADO.net Destination). Connect JSON Source to Destination and map source columns to target.

Facebook API Paging through dataset (i.e. Loop, Cursor)

In previous section you saw how to adjust few attributes so JSON Source can paginate through Facebook API response. To learn more about REST API Paging Methods – Click this link

Here is another link from Facebook Documentation for Paging

Manage/Write/Update Facebook data using REST API

If you wish to create new POST  or manage data using REST API then you most likely need to use REST API Task rather than JSON Source Connector. REST API Task also supports uploading media files.

Facebook Graph API Reference:

Facebook Graph REST API reference link

Conclusion

Facebook data integration can be quite challenging without right set of tools. SSIS PowerPack provides complete toolset for any REST API integration related scenarios. Try it yourself and find out all hidden features not discussed in this article.

Similar articles

Read Twitter data in SSIS using REST API Task and JSON Source – OAuth2 Protocol

How to get data from Google AdWords using SSIS

Posted in SSIS JSON Source and tagged , , , , , , , .