Read / Write Smartsheet data using SSIS REST API Call

Introduction

Smartsheet REST API IntegrationIn this post you will learn how to Read / write Smartsheet data using SSIS (Drag and drop approach without any coding).  We will use SSIS JSON/ REST API Source to extract data from Smartsheet API and use SSIS Web API Destination to write data to Smartsheet. This approach is similar to our previous blog where we described how to read and write Google sheet using SSIS

Now let’s look at step by step approach to call Smartsheet REST API.

 

Prerequisites

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:

  1. SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it, if you haven’t already).
  4. (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.

Create Token to access Smartsheet REST API

First step to access smartsheet API in SSIS is create API Token. We will use this API token later on to read data from Smartsheet Sheet.

  1. Login to your Smartsheet account
  2. Click on Account > Apps & Integrations > Click API Access > Click Generate Token
    Create Smartsheet API Token for REST API Access

    Create Smartsheet API Token for REST API Access (Step 1)

    Generate new Smartsheet token (Step 2)

    Generate new Smartsheet token (Step 2)

  3. Once Token is generated copy that for later use (Make sure you treat this token as password and do not share with unauthorized user.

 

 

Getting started with Smartsheet API

In this article we will use few out of many API provided by Smartsheet. To get started with Smartsheet API concepts click here. To read full details about each Smartsheet API click here.

Step-By-Step – Import/Export Smartsheet data in SSIS

Now lets see how to read and write smartsheet data using SSIS.

Read data from Smartsheet using SSIS JSON / REST API Source (Load Smartsheet to SQL Server)

To read data from smartsheet you need to perform following steps.

Find Sheet ID for Smartsheet API access

  1. First you need to know how to find Sheet ID. Perform below steps to find Sheet ID.
  2. Goto your portal and open sheet you like to read. Right click on the Sheet Tab > Click Properties and Copy Sheet ID
    Find Sheet ID for Smartsheet API call

    Find Sheet ID for Smartsheet API call

Once you know Sheet ID. Now lets create SSIS package to extract data from Smartsheet and load into SQL Server.

  1. Create new SSIS Package or Open existing one.
  2. From control flow SSIS toolbox drag Data Flow task
  3. Double click Data flow
  4. From SSIS Toolbox drag and drop ZS JSON Source (for REST API / File)
  5. Double click JSON Source and configure following properties
    1. Enter Shartsheet API Access URL as below (Change last part with your own sheet id)
    2. Check use credentials and then create a new HTTP connection. Select Static Token / API Key and enter enter your token we got in previous section
    3. Click on Select Filter and pick Rows node or just type below text in the Filter textbox
    4. Now goto 2D Array Transformation Tab and set following settings
      1. Set Transformation Type to Complex 2-dimensional array
      2. Column Name Filter to $.columns[*].title
      3. Row Value Filter to $.cells[*].value

        SSIS Smartsheet API Configuration - Complex Array transformation (Read nested array)

        SSIS Smartsheet API Configuration – Complex Array transformation (Read nested array)

    5. Once everything is configured Click Preview. Click OK to save.
    6. Drag OLEDB Destination from SSIS Toolbox.
    7. Connect JSON SOurce to OLEDB Destination
    8. Double click OLEDB Destination and configure connection (e.g. Select SQL Server connection) and select Target Table or click New to Create new Table based on Source columns.
    9. Click on Mappings to map source columns to target
    10. Click OK to save
    11. Execute Package
      SSIS Example : Loading Smartsheet data into SQL Server (REST API Call)

      SSIS Example : Loading Smartsheet data into SQL Server (REST API Call)

 

Read Smartsheet data with Pagination option

Smartsheet API support options to read large amount of data in small chunks by using pagination options. ZappySys offers many ways to paginate for various API.

Disable Smartsheet Pagination

Many API endpoints in Smartsheet by default send you paginated response. Pagination requires some extra configuration so try to disable it if possible by using includeAll=true option in your URL as below. If you cannot includeAll for some reason then perform steps listed in next section.

Smartsheet Pagination in SSIS

To paginate Smartsheet response in SSIS JSON Source perform the following steps. Below steps assume that you trying to get all sheets by calling https://api.smartsheet.com/2.0/sheets  URL in JSON Source (Just like we explained in previous section). Before dataflow we have to add one step to enable pagination. Lets check.

  1. Go to Control Flow designer right click > Select Variables
  2. Create new string datatype variable called name it TotalPages. Set default value to 0
  3. Now drag ZS REST API Task from SSIS Toolbox
  4. Enter Following URL (we don’t need many rows to so reduced default page size to just 10)
  5. Now click on Raw Edit (Above Headers grid)
  6. Enter following header (Replace Token with your own token we obtained in the previous section)
  7. Go to Response Settings Tab
  8. Select Content Type Json and enter filter as $.totalPages
  9. Check Save response to Variable. Select Variable we created in Previous step (Only string datatype variables are visible)
    Save Total Page Count to SSIS variable

    Save Total Page Count to SSIS variable

  10. Click Test Request/Response to confirm it works
  11. Click OK to save
  12. Now let’s use this variable to configure MaxPageNumber setting in JSON Source. Using following way you can make any Data flow component property dynamic.
    Define expression on SSIS Data flow Component Property (Dynamic Value)

    Define expression on SSIS Data flow Component Property (Dynamic Value)

  13. Now connect your REST API Task to Data flow
  14. On the JSON Source go to Pagination Tab and enter following settings.
    Smartsheet API Pagination settings - SSIS JSON / REST Source

    Smartsheet API Pagination settings – SSIS JSON / REST Source

  15. That’s it. Now if you run it you will see all records will be pulled.
You can configure delay after each request [on Throttling Tab] if you want to slow down requests. This is usually helpful if you are getting too many API requests error at runtime.

Write data to Smartsheet using SSIS Web API Destination (SQL Server to Smartsheet Import)

Now lets see how to write some data to Smartsheet Sheet. We will read records from SQL Server and write to SmartSheet using ZS Web API Destination

For inserting multiple rows we can call this Smartsheet API Endpoint

Basically High level steps are.

  1. Find out Smartsheet Column ID to use along with API call
  2. Read Records from SQL Server using OLEDB Source
  3. Construct desired JSON for Add New Row API Call
  4. Send JSON to Smartsheet Add Row API  (Configure Bulk Options )

Find out Smartsheet Column ID

Very first step you have to perform is get Column ID which correspond to certain title. You can use following URL endpoint to get list of all columns (Change your Sheet ID). Make sure you append includeAll=true else it may only fetch 100 columns. You can save Column result to some database table to File to copy ID. Preview Grid also allows to copy cell.

Get Smartsheet Column ID using API call in SSIS JSON Source

Get Smartsheet Column ID using API call in SSIS JSON Source

Configure SQL Server Source (OLEDB Source)

Once you have column ID you can drag OLEDB Source from SSIS Toolbox and configure to read from SQL Server. Below is sample source query to extract data from SQL Server.

Configure SSIS Template Transform

Once SQL Source is configure we are ready for next step. SSIS PowerPack v2.6.4 and later introduced new SSIS transform called Template Transform. This transform allows you to produce desired JSON /XML from single input record by using Column name as placeholder anywhere in your text. This is much simpler method than constructing JSON using JSON Generator Transform

Here is the sample JSON we want to produce to insert into Sheet which contains 4 columns OrderID, CustomerID, EmployeeID and OrderDate.

 

Now lets configure SSIS Template Transform to create desired JSON Request for API call.

  1. Drag ZS Template Transform from the SSIS toolbox
  2. Connect OLEDB Source to ZS Template Transform
  3. Double click Template Transform. Paste above sample request in the Template Text
  4. Now highlight sample value you wish to replace from upstream by some input column.
  5. Click on Insert Placeholders > Columns >  Your Column
  6. Perform above steps for each value you wish to replace. You can also add use placeholder functions such as JSONENC after column name (useful if you expecting new line or special characters such as tab or double quotes in your input text ). Date formatting (e.g. yyyy-MM-dd ) also allowed. See below example after placeholders added.
  7. Here is final configuration of Template Transform
    Create JSON for API Request (POST) using SSIS Template Transform

    Create JSON for API Request (POST) using SSIS Template Transform

Configure SSIS Web API Destination (POST New Rows to Smartsheet)

Now lets configure last step. This will POST API request to insert multiple rows to Smartsheet. Previous template transform crafts request for one new row but later in this section we will enable Batch mode so we can JOIN multiple JSON and created Bulk requests in one go so we avoid many API call. Always use Bulk mode if API endpoint supports it.

  1. Drag and drop ZS Web API destination from SSIS Toolbox
  2. Connect previous Template Transform to your Web API Destination.
  3. Select new HTTP connection. When Prompted enter following URL on HTTP Connection UI (Replace your Sheet ID)
  4. In the input column for Body select TemplateOutput
  5. For Body you can enter Sample Body if you wish to Test at design time else leave it blank
  6. Select Body Content Type as application/json
  7. In the Headers enter Authorization Header like we did in previous section (Read from Smartsheet)
    Configure SSIS Web API Destination - Add / Insert Rows to Smartsheet (Bulk API call)

    Configure SSIS Web API Destination – Add / Insert Rows to Smartsheet (Bulk API call)

  8. Go to Batch Setting mode. Change following settings
    1. Check on Enable submitting multiple records
    2. For Body Header enter [
    3. For Body Footer enter  ]
    4. For Body Row separator enter , (i.e comma)
    5. You can change Body Batch Size to desired value (100 is recommended)
      Configure SSIS Web API Destination for Bulk API call (Smartsheet REST API - Insert multiple rows)

      Configure SSIS Web API Destination for Bulk API call (Smartsheet REST API – Insert multiple rows)

  9. That’s it run your package to test the entire flow.
    Loading data from SQL Server to Smartsheet using SSIS (Web API POST Example - Bulk Mode)

    Loading data from SQL Server to Smartsheet using SSIS (Web API POST Example – Bulk Mode)

 

Truncation related error

The most common error you may face when you run an SSIS package is truncation error. During the design time only 300 rows are scanned from a source (a file or a REST API call response) to detect datatypes but at runtime, it is likely you will retrieve far more records. So it is possible that you will get longer strings than initially expected. For detailed instructions on how to fix common metadata related errors read an article “How to handle SSIS errors (truncation, metadata issues)“.

Authentication related error

Another frequent error you may get is an authentication error, which happens when you deploy/copy a package to another machine and run it there. Check the paragraph below to see why it happens and how to solve this problem.

Things have gone bad: Error handling & debugging

Incidentally, bad things can happen. A remote server may go offline or your server may go out of memory. In any case, you may want to know when that happens and take actions accordingly. For that purpose, you have to redirect bad rows to some other destination. For this example, we will take and use Web API Destination, but basically, you can use any SSIS component:

Handling errors

  1. Add a Derived Column above Web API Destination with expression “(DT_WSTR,4000)ZS_JSON_OUT” and name it “JsonAsString”. This will let you see what JSON you are actually passing.
  2. Then add a database or file destination or use another Trash Destination for debugging purposes and redirect the bad rows (red arrow) from Web API Destination into it. Don’t forget to set Redirect row option for both, Error and Truncation columns:

    Redirect bad rows from <em>Web API Destination</em> to <em>Trash Destination</em> when load from SQL Server to Elasticsearch is failing. Add derived column JsonAsString to be able to read JSON you are using.

    Redirected failed requests from Web API Destination to a desired destination when loading from SQL Server to REST API Service is failing. Derived Column JsonAsString added to be able to read JSON which was passed to Elasticsearch

  3. Finally, add a Data Viewer for the red path, if you want to debug the flow. You will be able to see URL, JSON and the error message for each record. You may want to copy-paste ErrorMessage to Notepad if you want it to be more readable:

    Use Data Viewer to view HTTP requests that failed to be fulfilled in Elasticsearch

    Use Data Viewer to view HTTP requests that failed to be fulfilled.

NOTE: You can read more about redirecting rows in SSIS Error Handling (Redirect bad rows) article.

Debugging HTTP requests

A common thing you have to do when working with HTTP requests is to debug those requests; e.g. to check what headers, body or URL was passed. To test how things look behind the scenes we strongly suggest to use Fiddler – a popular web debugging tool.

Inside it, you can double-click the URL entry (Right side) to see Request and Response Panels. The top panel is Request (URL, Headers, Body) and Bottom Panel is Response. For https:// (secure URL) make sure you enable HTTPS option in Fiddler (Tools > Options > HTTPS > Check Decrypt https request):

Debugging Web API call using Fiddler in SSIS

Debugging Web API call using Fiddler in SSIS

Conclusion

Smartsheet API provides great way to automate data read/write. However to call Smartsheet API  you have to use SDK / coding approach (e.g. C#, Java, Python, Ruby). Luckily ZappySys SSIS PowerPack provides great way to integrate any Smartsheet API call via simple drag and drop approach without coding. Try  SSIS PowerPack for free and call virtually any REST API in few clicks.

Keywords: Import smartsheet into sql server | export smartsheet to sql server | ssis smartsheet read | ssis smartsheet write | reading smartsheet data using API call | write to smartsheet

Posted in REST API Integration, SSIS JSON Source (File/REST), SSIS REST API Task, SSIS Template Transform, SSIS WEB API Destination and tagged , , , .