When it comes to REST, the possibilities are virtually limitless, with new connections happening almost daily. Task Factory’s REST Source and Destination component can be configured in just seconds to easily get data from or write data to web RESTful sources.
Today we’re looking at the REST Source inside of Task Factory to connect to a tool that can be used for customer lists, tables, and more; Google Sheets.
To get started, all you need is Task Factory and a developer account at developers.google.com and some sheets of data to work with. Maybe you can upload an Excel file and use it as your own example. By using a REST Source you’ll be able to apply powerful SSIS components to move, upload, download, and manipulate your data however you see fit.
Let’s dive in and see how simple it is to configure the REST Source once we’ve gotten our API keys, Secret, and Token from Google.
- First, I set up my connection manager and used a GET Statement (since I'm using Google Sheets as a SOURCE).
- Next, I put in my UR, consisting of my spreadsheet, values, sheet number and rows.
- Since I prefer JSON, that is the format that I chose to have my results returned.
- Then I go into Test API and Preview Data.
- Under the RAW response, the results from the REST call need to be unpacked from inside of an array.
- I’m looking to target specific items inside of that array, so I grabbed the token ‘values,’ then went into JSON properties and used that as my JSON path.
- By utilizing the token paths inside the RAW response, I was able to iterate through the array every time and get all the items I wanted (i.e. order data, sales region, sales rep, etc.)
- Now my RAW response is neatly formatted in the Results Grid as a table.
- From there, I chose to make it an Excel destination (which in Task factory is 64-bit native).
- I select the sheet I have in mind and connect it to my Excel document and go into columns and grab every item that I want to target.
It’s that simple. There is a wealth of Google projects you can connect to using REST Source and feel free to insert or update to any destination you choose. And you can do this quickly with Task Factory’s Upsert Destination.
Looking to improve SSIS? Task Factory’s 65+ custom components can increase productivity, improve performance and increase your ROI. Visit our website to download your free trial today to try out the REST Source, as well as all the other fantastic components, within Task Factory.