OVERVIEW

Better Bathrooms is an independent bathroom retailer located in the United Kingdom. The company markets their products through physical showroom locations as well as through online ordering. To support their business, they take advantage of many online platforms to sell and market their products including external marketplaces, and consumer review sites. With pertinent business data stored on multiple platforms, Better Bathrooms requires a robust solution to extract and translate that data to their internal servers.

Better Bathrooms logo

 Target

Gathering data from multiple applications and importing it into their SQL server

 Challenge

  1. Dealing with new and regularly changing APIs
  2. Working with JSON format

 Required SSIS Integration Toolkit

SSIS Productivity Pack

Getting the SSIS Productivity Pack enabled us to build connections to numerous API’s in a timely fashion and pull the data into our data warehouse. The KingswaySoft support team are outstanding and have been able to resolve any issues we’ve experienced.

~Philip Schonewille
Head of Systems
Better Bathrooms

THE CHALLENGE

Better Bathrooms was previously using a tool that allowed them to extract data from an API and write it to a flat file which could then be brought into their SQL Server. However, they started experiencing limitations with this solution as they needed to work with new APIs, particularly when it came to extracting data from APIs feeding data in JSON format. Their existing solution did not have a way to interpret the incoming JSON document and translate it into structured data that could be consumed by their database system.

THE SOLUTION

SSIS Integration Toolkit

SSIS Productivity Pack

The Better Bathroom team had extensive SQL & SSIS experience but were not comfortable enough to develop their own integration with the APIs they needed to work with, nor did they have the time and resources to custom develop so many integrations. They looked towards SSIS add-on solutions that would allow them to take advantage of their existing SSIS skill set. Better Bathrooms settled on SSIS Productivity Pack due to the solution’s ease of use and the responsive support they received from the KingswaySoft team. Working with a large number of APIs, Better Bathrooms quickly made use of the HTTP Connection Manager and were pleased to find the OAuth Token Manager made token creation and refreshing very simple. From there, Better Bathrooms were able to use the JSON and XML source components to retrieve data from the proprietary APIs and easily break up the hierarchical data structure to produce column data they could write to their SQL Server.

Working with such a large number of unique APIs offered some additional challenges. Better Bathrooms was able to work through the various intricacies of individual APIs with the KingswaySoft team by taking advantage of the many features within SSIS Productivity Pack components. This included the ability to send HTTP header information when generating & refreshing OAuth tokens, as well as the ability to page through source data in a robust fashion when working with REST or SOAP endpoints that use JSON or XML.

THE RESULTS

So far, Better Bathrooms has been able to utilize SSIS Productivity Pack to extract XML and JSON data directly from third-party SOAP and REST APIs including ZenDesk, eBay, Amazon, review sites such as TrustPilot, and their shipping company, Cardinal. SSIS Productivity Pack makes it easy to retrieve data from multiple platforms and write it to their internal servers to better manage their business data. By using SSIS Productivity Pack they are able to significantly reduce their development time as they integrate with more APIs. Better Bathrooms also makes use of the Premium Excel components included within the SSIS Productivity Pack for better flexibility when working with Excel files within SSIS, and for the support of 64-bit runtime. As their development with SSIS Productivity Pack continues, Better Bathrooms plans to take advantage of the Google Ads and Analytics components as well as using the JSON and XML components to integrate with more APIs.