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
- First, you will need to have SSIS installed
- Secondly, make sure to have SSDT
- 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:
- you can directly login to the LinkedIn site with your credentials, and redirect to the LinkedIn Developer Console for creating an app.
- you can open MyApps Page.
- Click on Create Application button, the new Application form appears, Fill it up.
- 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.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.
- Create a new SSIS Project or Open Existing Project in Visual Studio (File > New > Project > Business Intelligence > Integration Services)
- Once SSIS Package is open, Goto Data Flow Section, Drag and Drop ZS JSON Source Component from the SSIS Toolbox.
- 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:
1https://api.linkedin.com/v1/people/~:(id,firstName,lastName,headline,num-connections)?format=json - Check Use Credentials Option and select a new ZS-OAUTH connection from the drop-down.
- 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).also, Enter your Callback URL in Advanced Tab.
NOTE: If you are experiencing problems when authenticating, select Custom OAuth Provider and follow Microsoft Docs on how to authenticate. - Click on Preview button to see the response data.
- Click OK to save JSON Source settings.
- Drag any destination (i.e. OLEDB Destination) from your SSIS toolbox. For demo purpose, we will load LinkedIn data into SQL Server Table.
- Configure OLEDB Destination (Select Connection and Target Table). You can Create new Table for the destination by clicking NEW next to Table drop-down.
- Click on the Columns Tab to configure Mappings. Click OK to save.
- And that’s it. Execute your package to Load LinkedIn data to SQL Server.
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
- First of All, Goto Control Flow Section, Drag and Drop SSIS REST API Task from the SSIS Toolbox.
- Double click on that Component to Edit the Configurations. Configure the task like the below-attached screesnshot.
12345678910Request URL:https://api.linkedin.com/v1/people/~/shares?format=jsonRequest Body:{"comment": "Hello, This is test message posted by Hardik from SSIS Package","visibility": {"code": "anyone"}} - now you are done with the configurations. you can do Test Request/Response. See the below-attached request/response screen.
- Goto your LinkedIn Profile, you can see the latest Post which is posted by us with the use of ZappySys and SSIS.
- You can Share a Post with a Specific Values too. See the Below example we have added title, description, submitted URL, submitted image URL.
1234567891011121314Request Body:{"comment": "Check out ZappySys's Latest Product ODBC PowerPack ","content": {"title": "ODBC PowerPack","description": "ODBC PowerPack is a collection of high-performance ODBC API Drivers for various API data source (i.e. Web API, REST API, JSON, XML, SOAP, OData). With simple and familiar SQL query language you can read/query data from API sources or JSON / XML Files inside your favorite Data Engine / Reporting / ETL tools (i.e. SQL Server, Power BI, Tableau, Qlik, SSRS, Informatica, Excel, SSIS). Using our API drivers you can consume data from local files or connect to virtually any API data source (internal or cloud API) such as Amazon AWS API, Salesforce API, Google API (i.e. Analytics, AdWords, DoubleClick), Facebook, Twitter, Zendesk, eBay and many more.","submitted-url": "https://zappysys.com/products/odbc-powerpack/","submitted-image-url": "https://zappysys.com/wp-content/uploads/2017/11/slider-odbc-powerpack-rest-api-json-xml-drivers-1.png"},"visibility": {"code": "anyone"}} - see the latest Post which is posted by us with the use of ZappySys and SSIS.
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.
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:
- LinkedIn Documentation for API
- Lending Page for REST API Task, you can also find Tutorial Video here.
- Lending Page for JSON Source Component, you can also find Tutorial Video here.
- Help File: Documentation of SSIS REST API Task and JSON Source Component.