![\"\"](\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/10\/twitter-api-integration.png\")
<\/a>In this article you will how to Read Twitter data in SSIS<\/em> using SSIS JSON Source<\/a> and SSIS REST API Web Service Task<\/a>. You will also learn about latest OAuth 2.0 Protocol to simplify REST API access.<\/p>\nTwitter REST API Authentication<\/h2>\n
In order to fetch any data from twitter using OAuth REST API calls you have to obtain two Keys (Consumer Key [Like UserID] and Consumer Secret [Like Password]). There are mainly two methods to read data from Twitter. Using Application-user authentication<\/strong> and Application Only authentication<\/strong>. Use correct method depending what type of information you want to pull from twitter. Most common method is Application-user authentication<\/strong> . If you want to read more about other methods then click here<\/a><\/p>\n In this method you will need twitter user account to connect Twitter API. Once first time authorization is done you don’t have to re-authenticate. Certain type of API calls only allowed by this method (such as POST new tweet using API). For any API call in Twitter very first step is create OAuth App (i.e. Register new App in Twitter developer portal).<\/p>\n To make your life easy ZappySys provides default App for certain OAuth providers (e.g. Google, Twitter). If you decide to create your own app for whatever reason then check next section on how to register twitter OAuth Application. See below screenshot how to use Twitter OAuth connection using Default App. Once you click Generate Token it will prompt you to login using Twitter account and then you can grant permission.<\/p>\n Step-By-Step<\/strong><\/p>\n Dragging and dropping Data Flow Task into Control Flow<\/p><\/div><\/li>\n Drag and Drop JSON Source Component<\/p><\/div><\/li>\n Call Twitter REST API in SSIS – using OAuth Connection (Default App)<\/p><\/div>\n For some reason if you dont want to use Default Twitter App then you can register Custom App. It requires few extra steps listed in next section but it wont take more than few minutes. In Custom App option you have to specify ClientId and Client Secret (in above screenshot). Once you click Generate Token it will prompt you to login using Twitter account and then you can grant permission.<\/p>\n Now lets look at how to register OAuth Application for Twitter API access.<\/p>\n Goto https:\/\/apps.twitter.com\/app\/new<\/a> and create new app by providing necessary information. Don’t get confused by calling it App, its just way in twitter to create multiple API Access Keys so you can grant different level of access to different users. Once app is created you will be taken to page where your Consumer Key and Secret Key will be listed.<\/p>\n Create Twitter Application – REST API Access for Developer<\/p><\/div>\n Once app is created you can go to Keys and Access Tokens<\/b> tab. Here you will find Consumer Key and Consumer Secret. Obtain Twitter API Consumer Key and Consumer Secret – REST API Access for Developer<\/p><\/div>\n For some reason if you don’t want to use Twitter user account to access data (e.g. Giving access to consultant so he can access you company Twitter Account via API). In this scenario you have to use Application Only method. In this method you don’t authorize application using Login form.<\/p>\n Here is our basic flow to access twitter data.<\/p>\n Once we create SSIS Package – First step to access any twitter data will be get Access Token. As you can see from below screenshot we have called https:\/\/api.twitter.com\/oauth2\/token service URL with POST method. Notice how we have supplied POST data and 2 headers. Authorization header contains BASE64 encoded value of YourConsumerKey:YourConsumerSecret<\/strong><\/p>\n SSIS Twitter REST API Get Token<\/p><\/div>\n Once we have Authentication Token we are ready pull twitter data<\/b> using JSON Source. Check below screenshot how we have supplied Token in Authorization Header. JSON Source can make your JSON look like normal table (It also de-normalize nested JSON into flat dataset. If you want to extract subset of JSON then simply specify JSON Path expression e.g. $.data.users[*]\n Read JSON response of Twitter REST API in SSIS<\/p><\/div>\n You can easily connect your SSIS JSON Source<\/a> to OLEDB Destination if you want to load Twitter Data to some RDBMS such as SQL Server, MySQL.<\/p>\n Most of REST API limit total data sent in single response. So if you wish to get all records then you have to loop through multiple results. Twitter provides looping mechanism using CursorClick here to read more<\/a><\/p>\n In our case SSIS JSON Source<\/a> Supports Paging very well so we are covered. To loop through multiple result sets of twitter data simply configure following 3 properties. See below screenshot.<\/p>\n Twitter REST API – Paging Example -Loop through resultset using cursor<\/p><\/div>\n Twitter does not allow you request too much data too quickly so be careful how many requests you make :). Check their official page on twitter Rate Limit for REST API. You can add delay after each request if you doing pagination. Go to Throttling tab<\/strong> of JSON\/XML Source. You can change setting there based on API restriction. For example if API allows only\u00a030 requests per minute then adding 2 seconds delay will make sure you wont exceed\u00a030 requests in 1 minute.<\/p>\n To learn more about rate limit of Twitter API check this table<\/a><\/p>\n Twitter returns date in the following format e.g. Fri May 03 15:22:09 +0000 2013 to parse this to correct date\/time datatype you can use Date\/Time Handling Tab of JSON\/XML Source.<\/p>\n Change Custom Date Format as below and preview Twitter data.<\/p>\n Parsing Twitter API date\/time format<\/p><\/div>\n Below is sample SSIS package So you have now seen how easy it is to access twitter data with OAuth 2.0 using SSIS JSON Source<\/a> and SSIS REST API Web Service Task<\/a>. We have also seen how to loop through large resultset using inbuilt Paging support of JSON Source.<\/p>\n","protected":false},"excerpt":{"rendered":" Introduction In this article you will how to Read Twitter data in SSIS using SSIS JSON Source and SSIS REST API Web Service Task. You will also learn about latest OAuth 2.0 Protocol to simplify REST API access. Twitter REST API Authentication In order to fetch any data from twitter using OAuth REST API calls […]<\/p>\n","protected":false},"author":1,"featured_media":1660,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[225,10],"tags":[215,6,13,24,60,25,101,3,4,23],"yoast_head":"\r\nMethod\u00a01 –\u00a0Read Twitter data in SSIS using Application-user authentication<\/h2>\n
Using Default OAuth App Created by ZappySys<\/h3>\n
\n
<\/a><\/a>https:\/\/api.twitter.com\/1.1\/statuses\/user_timeline.json?screen_name=zappysys&count=4<\/pre>\n<\/li>\n
<\/a>Using\u00a0Custom OAuth App Created by you<\/h3>\n
Register\/Create OAuth Application\u00a0for twitter API Access<\/h3>\n
<\/a>Obtaining Consumer Key and Consumer Secret from Twitter<\/h3>\n
\nConsumer Key can be Public but Consumer Secret must not be shared (think it like a password).<\/p>\n<\/a>Method\u00a02 – Read Twitter data in SSIS\u00a0– Application-only authentication<\/h2>\n
\n
Application Only Authentication using REST API task – Get Access Token<\/h3>\n
<\/a>Fetch data from Twitter using JSON Source – Deformalize nested JSON<\/h3>\n
<\/a>Load Twitter JSON data to SQL Server<\/h3>\n
Handling paging of large REST API result set with twitter data – looping\/cursoring<\/h2>\n
<\/a>Word of caution about too many requests<\/h2>\n
Handling Twitter API Date Format (Parse\u00a0Twitter Date\/Time)<\/h2>\n
<\/a>Download related files<\/h2>\n
\nTwitter Demo SSIS 2012<\/a><\/p>\nConclusion<\/h2>\n