This is part two in a series of extending SSIS for the enterprise with complementary Microsoft products. Specifically, we are going to look at how we can extend an SSIS solution to go through DMZs and firewalls across the Internet to reach and capture data regardless of the SQL Server schema type.
In this scenario, Perficient’s client had resorts across the Americas and did not have insight at the corporate level who was staying at the resorts and how money was being spent. Marketing wanted to get more in-tune with their guests so that they could create programs and promotions that were better tailored to guests’ needs by geographical location. With a limited budget they called on Perficient for help with their problem of integrating the resorts guest stays. After understanding the needs and explaining what could be done, Perficient architected the following solution with SSIS, SQL Server, and .NET Services. Other options were not feasible such as installing SSIS at the remote locations because of security constraints of what ports SSIS uses.
Since each resort could have a different reservation system or use different elements in the same system, the service had to be generic enough so that code did not have to be rewritten for each resort. To solve this issue of write once and run anywhere, the service installed at the resort leveraged a configuration file of one to many SQL queries that were specific to the reservation system. Using a loop, the service connected to the reservation system and populated an ADO.NET dataset for each of the query results. Since the dataset is XML based, it was easily passed back through the firewall and DMZ through already opened ports of simple internet protocols at headquarters to an ASP.NET web service. Once back at headquarters, the datasets were re-hydrated and saved to stage tables with a simple save command of the dataset object – no code was needed that was specific to a reservation implementation. Once the raw stage data was back into SQL Server, SSIS took over and transformed the different schema types into one conformed dimensional model. Previous to this project, the company was maintaining separate star schemas based on the reservation system type for financials but decided to migrate all of the data warehousing over to the consolidated approach that was used for the guest information for improved BI analytics.
With this scenario, one of the reservations systems was asking to charge 4 times the amount the above system cost to build by Perficient. And they would have only supported only their reservations system type. Having an understanding of many different technologies, Perficient was able to create a system that had more robust features (support any reservation type) and deliver the project at a fraction of the cost of other alternatives.
For companies that do not have the infrastructure to create a solution like the one above, look to an Azure cloud solution to host SSIS and the centralized SQL Server database. At this time, to achieve the features of Microsoft BI, a VM needs to be created to enable the BI features of a full SQL Server installation. An Azure solution such as this would lower the entry level cost for Microsoft BI by substituting a subscription model for hardware and software versus the traditional model of setting up all of the infrastructure upfront at a datacenter or within the enterprise.
Stay tunes for next blog in this series to see how SSIS’ XML processing was extended to handle complex XML types for a leader in the financial industry.
Andrew Holowaty
MBA, PMP, MCSD, MCAD, MCDBA, MCTS
Advanced Data Integration with SSIS, Part 1
Advanced Data Integration with SSIS, Part 3
Advanced Data Integration with SSIS, Part 2
by Andrew Holowaty on August 10th, 2013 | ~ minute read
