How to read LinkedIn data in SSIS – Call REST API / Load to SQL Server

Introduction

In this blog, we will learn How to read LinkedIn data in SSIS and load into SQL Server Table, along with few other topics such as how to register an OAuth App for LinkedIn REST API Call, how to retrieve basic profile data from LinkedIn, how to share a post on LinkedIn with SSIS.

We will go through the steps to read data from LinkedIn and Load into SQL Server using  SSIS REST API Task and SSIS JSON Source Connector.

In nutshell, this post will focus on how to call LinkedIn API using SSIS.

So let’s get started.

Prerequisite

  1. First, you will need to have SSIS installed
  2. Secondly, make sure to have SSDT
  3. Finally, do not forget to install ZappySys SSIS PowerPack

What is LinkedIn

LinkedIn is a social network for the business community. It provides an online platform for the professionals to connect with past and current colleagues. A user can increase their number of business connections, network within their industry, discuss business ideas, search for jobs and look for new hires.

Getting Started

In order to start, we will show several examples. ZappySys includes an SSIS REST API Task and SSIS JSON Source Connector that will help you to call LinkedIn API, retrieve basic profile data from LinkedIn, share a post on LinkedIn with SSIS and do many more actions using REST API. To learn more about LinkedIn API check this help file.

Create LinkedIn App (for OAuth)

To access LinkedIn data using REST API call you need to create a LinkedIn app. Follow steps mentioned below to register LinkedIn OAuth App:

  1. you can directly login to the LinkedIn site with your credentials, and redirect to the LinkedIn Developer Console for creating an app.
  2. you can open MyApps Page.
    LinkedIn My Apps

    LinkedIn My Apps

  3. Click on Create Application button, the new Application form appears, Fill it up.
    Linked In: Create New Application Form

    Linked In: Create New Application Form

  4. Once you are done with the Creation of an App, you can see the Authentication tab.
    Enter Authorized Redirect URL there and you have your App’s Client ID and Client Secret.

    LinkedIn App: Authentication Tab

    LinkedIn App: Authentication Tab

    Now we are ready with an App. Let´s start with an example. In this example, we will show how to retrieve basic profile data from LinkedIn and share a post on LinkedIn with SSIS.

Load LinkedIn data to SQL Server using SSIS JSON Source

Now once you registered OAuth app in the LinkedIn website we can move to SSIS Part.

  1. Create a new SSIS Project or Open Existing Project in Visual Studio  (File > New > Project > Business Intelligence > Integration Services)
  2. Once SSIS Package is open, Goto Data Flow Section, Drag and Drop ZS JSON Source Component from the SSIS Toolbox.
    Drag and Drop JSON Source Component

    Drag and Drop JSON Source Component

  3. Double click on that Component to Edit the Configurations. Select File path or web URL from the Access Mode drop-down.
    Enter Web URL like this:
    JSON Source: Enter the Request URL

    JSON Source: Enter the Request URL

  4. Check Use Credentials Option and select a new ZS-OAUTH connection from the drop-down.
  5. Configure new OAuth Connection for LinkedIn as below.
    Basically, you need to enter 3 pieces on the General tab (i.e. ClientId, ClientSecret, Scopes) and one more info on the Advanced tab (i.e. callback URL ). If you are not sure what is Scope then check this linkedin API documentation. For each API page you will see which permission needed. Just include the desired scope on ZappySys OAuth Connection (see below).

    LinkedIn Connection: OAuth Connection Manager

    LinkedIn Connection: OAuth Connection Manager

    also, Enter your Callback URL in Advanced Tab.

    LinkedIn Connection: OAuth Connection Manager Advanced Tab

    LinkedIn Connection: OAuth Connection Manager Advanced Tab

    NOTE: If you are experiencing problems when authenticating, select Custom OAuth Provider and follow Microsoft Docs on how to authenticate.
  6. Click on Preview button to see the response data.
    JSON Source: Preview basic profile of a LinkedIn user

    JSON Source: Preview basic profile of a LinkedIn user

  7. Click OK to save JSON Source settings.
  8. Drag any destination (i.e. OLEDB Destination) from your SSIS toolbox. For demo purpose, we will load LinkedIn data into SQL Server Table.
  9. Configure OLEDB Destination (Select Connection and Target Table). You can Create new Table for the destination by clicking NEW next to Table drop-down.
  10. Click on the Columns Tab to configure Mappings. Click OK to save.
  11. And that’s it. Execute your package to Load LinkedIn data to SQL Server.

Share a Post on LinkedIn (Use SSIS REST API Task)

Now let’s look at one more example to call LinkedIn REST API. This time we will share a post on LinkedIn Page using REST API call in SSIS. For this, we will use SSIS REST API Task

  1. First of All, Goto Control Flow Section, Drag and Drop SSIS REST API Task from the SSIS Toolbox.
    Drag and Drop REST API Task

    Drag and Drop REST API Task

  2. Double click on that Component to Edit the Configurations. Configure the task like the below-attached screesnshot.
    REST API Task: Configurations

    REST API Task: Configurations

  3. now you are done with the configurations. you can do Test Request/Response. See the below-attached request/response screen.
    Share Post on LinkedIn: Test Request/Response

    Share Post on LinkedIn: Test Request/Response

  4. Goto your LinkedIn Profile, you can see the latest Post which is posted by us with the use of ZappySys and SSIS.
    Share a Post on LinkedIn: LinkedIn Activity Screen

    Share a Post on LinkedIn: LinkedIn Activity Screen

  5. You can Share a Post with a Specific Values too. See the Below example we have added title, description, submitted URL, submitted image URL.
    Share a Post on LinkedIn with Specific Values

    Share a Post on LinkedIn with Specific Values

  6. see the latest Post which is posted by us with the use of ZappySys and SSIS.
    Share a Post with Specific values on LinkedIn: LinkedIn Activity Screen

    Share a Post with Specific values on LinkedIn: LinkedIn Activity Screen

VERY Important – About Refresh Token / Access Token

As of now Linkedin allows only one active Access Token. Which means if you Regenerate token on OAuth connection manager then any previously generated token becomes invalid so always use just one Linked Shared Connection in your package / solution or parameterize Package level connection with same access token.

https://developer.linkedin.com/docs/Refresh-Tokens-with-OAuth-2#

Access Token you generate on OAuth Connection manager expires after 60 days and after 60 days unfortunately you have to re-authorize (3-Legged) to get new token. This is limitation imposed by linkedin  which may change in future but unless you are authorized partner by LinkedIn you have to do this way. See below notes.

LinkedIn has introduced Refresh Tokens with OAuth 2.0.  This feature is currently available for a limited set of partners. It will be made GA in the near future.

 

Conclusion

In this blog, we learned how to Create a LinkedIn App, retrieve basic profile data from LinkedIn and share a post on LinkedIn with SSIS using ZS JSON Source Component and SSIS REST API Task in a very simple way. You can achieve many more functionalities with the use of both of these tools. Check our blogs/articles on REST API Task https://zappysys.com/blog/tag/ssis-rest-api-task/ and JSON Source Component https://zappysys.com/blog/tag/ssis-json-source/ to find out what these tools are capable of more.

References

Finally, you can use the following links for more information about the use of LinkedIn API with our tools:

Posted in REST API Integration, SSIS JSON Source (File/REST) and tagged , , , , .