How to get data from Facebook in SSIS using REST API

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.

	REST API Task
	OAuth Connection Manager
	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.

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

Navigate to https://developers.facebook.com/tools/explorer
Click Get Token button, select permission you really care (To get PageID you don’t need to check anything).
Type /YourPageName in the API Url bar
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

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.

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

Get Page Access Token (optional)

Accessing data of your Facebook page requires to generate a Page Access Token, follow the steps below to get it. If you are using non-page API methods, then you can freely skip this step. In SSIS Designer drag and drop REST API Task.

Then use the same OAuth connection we created in the previous step.
Proceed with setting the URL using this format:
https://graph.facebook.com/v2.5/REPLACE_WITH_PAGE_ID?fields=access_token
Click Test Request/Response button and then copy the Page Access Token to a Notepad.

Getting Facebook Page Access Token using Facebook Graph API and SSIS

Get Long-Lived Access Token

Most likely, once finished, you will deploy the SSIS package to your SQL Server and then set a schedule to run it daily or hourly. With ordinary Access Tokens you will run into a problem after 30 or 60 days because these tokens will expire after a similar time and your SQL Job will fail. To avoid that, we can generate a Long-Lived Access Token which will live for 60 days, as long as you use that token (and running the package is using it). Perform these steps to generate a Long-Lived Access Token:

Firstly, you will need to find out your Facebook User ID. To do that, call this URL in Facebook Graph Explorer or using REST API Task with configured OAuth Connection Manager:
https://graph.facebook.com/v2.5/me
Copy the ID from the response.
Then drag and drop another REST API Task and configure it like this, if you want to generate a Long-Lived Page Access Token:
Getting Facebook Long-Lived Page Access Token using Facebook Graph API and SSIS

Otherwise, if you want to generate a non-page Long-Lived Access Token, use this configuration:

Getting Facebook Long-Lived Access Token using Facebook Graph API and SSIS

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.

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

https://graph.facebook.com/v2.5/<strong>YOUR_PAGE_ID</strong>/insights/page_impressions_organic_unique

1	https://graph.facebook.com/v2.5/<strong>YOUR_PAGE_ID</strong>/insights/page_impressions_organic_unique

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

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

Drag Data Flow Task from SSIS Toolbox and double click to view Dataflow designer
Drag ZS JSON Source from SSIS Toolbox. Double click to configure.
In the AccessMode select “File Path or Web URL”
Enter URL:
https://graph.facebook.com/v2.5/YOUR_PAGE_ID/insights/page_impressions_organic_unique?access_token=REPLACE_WITH_LONG_LIVED_ACCESS_TOKEN
NOTE: You may use variables and parameters in the URL e.g. http://mydomain/{{User::MyPath}}:
Getting Facebook Page data using Facebook Graph API and SSIS
Click on Select Filter and select values node and click OK. It may produce filter expression like below.
You can set Max Rows option to desired value if you don’t want to extract all rows (0=Unlimited)
Goto Paging Tab and enter $.paging.next in the “Next Cursor” field as below
Getting page data from Facebook and paginating through the results using SSIS
Click on Columns tab if you wish to see/change metadata (You can change size / datatype there and Lock metadata to prevent auto refresh)
Now click Preview to see sample data and then exit preview and click OK to save UI changes

Load Facebook data into SQL Server

If you wish to save Facebook data into SQL Server or any other target like Oracle, 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 the 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.

Deployment to Production

In SSIS package sensitive data such as tokens and passwords are by default encrypted by SSIS with your Windows account which you use to create a package. So SSIS will fail to decrypt tokens/passwords when you run it from another machine using another Windows account. To circumvent this when you are creating an SSIS package which uses authentication components (e.g. an OAuth Connection Manager or an HTTP Connection Manager with credentials, etc.), consider using parameters/variables to pass tokens/passwords. In this way, you won’t face authentication related errors when a package is deployed to a production server.

Check our article on how to configure packages with sensitive data on your production or development server.

You may also check the article on how to make URL dynamic/parametric, so that you can pass Long-Lived Access Token as an argument in a SQL Job.

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.

References

Facebook Graph REST API reference link

https://developers.facebook.com/tools/explorer

https://developers.facebook.com/docs/facebook-login/access-tokens/refreshing/