This is part three in a series of extending SSIS for the enterprise with complementary Microsoft products.
Different industries have standards for data such as Accord for insurance, HL7 for medical, and Eagle-PACE for finance. These standards have a defined data model that can also be used for data exchange. One of the transports for the data exchange between companies is XML. In this scenario, we have a hedge fund replacing their backend operations with data supplied by another financial institution so that the hedge fund can focus more on their core competency of creating and managing financial instruments versus backend data operations. The planned approach was for the bank to supply data about trades in the form of XML to the hedge fund. For the technology implementation, the client chose Integration Services since they were already using SQL Server and SSIS comes free with the SQL Server Engine. Over the course of the implementation, we ran into constraints where SSIS cannot handle some complex types of XML. When this happened, SSIS was extended by using a Control Flow Script Task and a sprinkle of .NET. .NET can call an XSLT to transform the XML into a simpler structure so that the XML Source of SSIS can consume the incoming document. After applying the XSLT to the XML document, SSIS constructs finished the job of importing the data from the bank.
By lowering the Total Cost of Ownership through making SSIS part of the SQL Server offering, Microsoft is revolutionizing data integration and has made it possible for all organizations to do it themselves versus spending millions of dollars on tools or outsourcing to third parties who were once the only ones able to do the job.
Andrew Holowaty
MBA, PMP, MCSD, MCAD, MCDBA, MCTS
Advanced Data Integration with SSIS, Part 1
Advanced Data Integration with SSIS, Part 2
Advanced Data Integration with SSIS, Part 3
by Andrew Holowaty on August 27th, 2013 | ~ minute read
