Yesterday I posted in reply to Philip Howard's article on The Register about Analysis Services' support for federated queries/Enterprise Information Integration (EII) through the use of data source views.
As I thought about it more I realised that SQL Server Integration Services (SSIS) also provides support for the very same - and its probably better than a data source view.
Federated queries are, at a very abstract level, about integrating data from different systems so they appear as though they are in the same system. Right? Well, isn't that exactly what SSIS does, integrate data from different systems? (The answer you're looking for here is "Yes" :)
The SSIS data-flow pipeline is designed so that data from differing source systems can be amalgamated, edited, filtered and manipulated in the same place. "But Jamie", I hear you ask, "its all very well getting data from different systems and joining it together etc...but SSIS requires that the data is placed somewhere at the end of the data-flow and is therefore fundamentally not EII".
Actually no. Its a currently little known fact about SSIS that the data does *not* have to actually flow to a data sink as described above. SSIS contains a destination adapter called a Datareader. The Datareader destination adapter allows any data that flows to it to be exposed as a .Net datareader component and therefore able to be read by anything that wants to consume it. SSIS itself doesn't actually know or care where the data goes, all it knows is that it is serving up data, possibly from differing source systems, to be consumed elsewhere.
I may be wrong but to me, that almost sounds like a dictionary definition of a federated query doesn't it?
One typical use of this would be to have a Reporting Services report access an SSIS data-flow thereby reporting directly out of disparate source systems without putting undue load onto those source systems. Another possibility could be to expose an SSIS data-flow as a web service thereby enabling us to integrate data from disparate systems into a Service Oriented Architecture (SOA).
I hope Mr Howard is reading this - I'd be interested to know whether this satisfies the requirements for EII and he seems to be a man in the know.
Donald Farmer regularly talks about SSIS as a platform for data integration rather than a pure ETL tool and the point is clearly in evidence here. SSIS is currently considered to be an ETL tool but what we are talking about here is fundamentally not ETL although still clearly realised through the available SSIS functionality.
-Jamie