For the last week (or unofficially last two weeks) I’ve been using SQL Server Integration Services to build the import process for the daily data exchange we have with our customers. The system is used to aggregate a fragmented market place after deregulation. The cost saving coming from many companies using a single source to do the grunt work (so taking that line of thought further, what value was gained by deregulation, but I digress)
The way data is exchanged is via flat files that are fixed position or comma separated. After seeing demo’s of SSIS at TechEd and the likes, it seemed a great tool to manage the data import/export process here. This would leave the core system to focus on aggregation and job management. The other tool that could have great impact is the Workflow Foundation.
The first challenge to over come is that in the data exchange files, many relational tables are merged and layed out by the related rows after each other (with foreign key relationships implied by order). This required that I build a custom source component that could parse the rows and output the separated table, but also insert the correct foreign keys. Well I’ve got this step completed with a hardcode format, and it works a treat inside SSIS. With the hurdle passed we now know we can process the different types of files we exchange with our current business partners.
Now at this point, the business decision of encapsulating our current business processes in SSIS, so the resulting data can be push directly in the database. For our current product, that’s a lot of work, with little clear gain. The major gains I see are the higher level view you get in SSIS would allow better understanding of the import process. We would separate the import process from the core product, allowing independent release cycles. Load would be removed from the SQL database (via stored procedures) to a potential different location to allow better load management.
At this point I think the management team is putting the idea to rest.
Things I’d still like to do with the custom component is make it configurable at design time via a GUI, with either external configuration files, or stored in the meta data of the SSIS package (haven’t worked out how to do this one yet). I’d be keen to release the component, to give back to the community.
I found the online MSDN really helpful and the people at the Microsoft Technical Forum where great, especially Jamie Thomson and Simon Sabin. The resources at sqlis.com were also really great.