Better Bathrooms

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.

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 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.