SSIS and StreamInsight Integration

Have your ever heard of something called "StreamInsight Multi Stream Transform" ? I am sure the answer would be "No", else you know what I am talking about. When I read about this subject, I instantly started thinking about why I need to use StreamInsight from SSIS ( probably to calibrate the incoming / outgoing data stream from StreamInsight engine), and is it even possible. A technical mind immediately jumps at the implementation level as soon as the requirement subject strikes the mind. I immediately started thinking, whether I can use Script transform and hook into StreamInsight by some means. StreamInsight is one of the least explore technologies in SQL Server stack of technologies, and generally most of us have explored CPM (Corporate Performance Measurement) much more than CEP (Complex Event Processing).

SSIS is gradually evolving as a rich platform that can facilitate much more than the very definition of what we term as ETL.

1) Script Transform is the first window connecting SSIS to the managed .NET / unmanaged COM world. We already have established examples where we can use this transform to use services like Bing Maps, SSRS and others.

2) Data Correction transform is another upcoming transform that connects SSIS to SSDQS in SQL Server Denali. Considering this transform coupled with Data Profiler Task and Data Profile Viewer, SSIS has sufficient ammunition to target corporate data cleansing assignments.

3) The transform that I discussed above "StreamInsight Multi Stream Transform" is a transform that has been developed in a POC carried out by SQL CAT Team. Entire explanation of to and fro integration of SSIS and StreamInsight has been wrapped up in a whitepaper, which can be read from here. This whitepaper is under the umbrella of SQL Server 2008 R2, but one of the screenshots which shows SSIS implementation has the icons of SSIS that are only available in SSIS Denali CTP1, which means that it might have been tested in SSIS Denali. So my forensic sense tells me that, it should be very well possible in SSIS Denali too, and logically speaking it should.

I would wish that this transform becomes a part of SSIS Denali, or least the same can be made available on the SSIS Tasks and Components Gallery, so that professionals can leverage this transform instead of building it from scratch.