Exporting Trello Boards With JSON, T-SQL and SSIS, Part 1: Overview and Strategy

Introduction

Over the last couple of years, I’ve been exposed to various elements of DevOps and Agile Development. One element was the project management methodologies in use, in particular the use of Kanban to track each two-week development sprint.

My first exposure to Kanban was the use of Post-It notes on the conference room wall to track dozens of reporting requests for our 30-person Analytics team. The entire wall was covered and it took multiple days to categorize them into swim lanes, deduplicate them and delegate them to the various analysts. Eventually, it moved over to Fogbugz for an all-digital approach.

Since then, I’ve worked with Jira and am awaiting the rollout of SmartSheet for similar functionality. While I’m waiting, I started using Trello for my personal projects.

Trello (see second image at left) is very easy to work with. Once you setup an account, you can create a board, add your lists and cards and start tracking. The freeware version has all the essential features, but if you want to collaborate in a serious effort, you’ll want to acquire a business license.

Which brings me to my story of how I ended up using T-SQL, JSON functions and SSIS to supplement my Trello boards for project tracking…

Scenario

I came across a situation where I wanted to export the Kanban board to an Excel workbook as one sheet in an ensemble of related data.

I looked at the Export options and I discovered that the feature to Export to a CSV was disabled (see image at left). Not to worry: there’s an Export to JSON feature. I’ll just try that. I figure it’ll be easy to parse JSON into Excel.

As you can see from the image at the bottom left, this was not going to be straightforward. The JSON document isn’t just the lists and cards. It’s everything from board name down to list and cards, right down to every property and transaction. It’s a lot to extract but I just wanted to see the board name, the lists and the cards. And I needed to do this often.

Functional Requirements

I broke down what I wanted to do so that I can spend the least amount of time updating the workbook and the most amount of time making progress.

1. Must be able to extract data from a Trello board JSON export as-is
2. Must be able to reproduce the lists and cards layout of the board at the time of JSON export.
3. Must be able to write this reproduction to an Excel-friendly text file (just open and it’ll put each list into an Excel column)
4. Must be able to email the output with the name of the Trello board and export creation date in the subject line and the text file above as an attachment.
5. Must be able to run this unattended with files put into a drop zone folder and automatically archived after processing.

Technical Specifications

Armed with knowledge of SQL Server features, T-SQL, a bit of JSON functions, and SSIS for ETL packages, I came up with the following design…

1. Use of T-SQL stored procedure to
   1. load the JSON file into a variable using bulk insert capability.
   2. Use of JSON query functions to extract the list names, card names and the board name.
   3. Use CTEs, PIVOT operators and dynamic T-SQL to reproduce the board layout.
2. Use of a SSIS package to
   1. Check for source JSON files in a drop folder
   2. For each file in the folder…
      1. Move the file into a processing queue.
      2. Use a script task to
         1. get the file creation date
         2. run the stored procedure to render the board contents
         3. export the results to a CSV file replicating the appearance of the board.
      3. Email me using job board and creation date in the subject and attaching the export file.
      4. Archiving the source file.

In my next post, I’ll walk through assembly of the stored procedure to import the JSON file and reproduce the Kanban board layout.