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.

StreamInsight Tutorial , StreamInsight Video Tutorial , StreamInsight Webcasts , StreamInsight Labs and StreamInsight Presentations

In my views, StreamInsight is one of the most undiscovered enhancements of SQL Server 2008 R2. StreamInsight is a development platform for targeting complex event processing (CEP), and though this is newly introduced in SQL Server 2008 R2, but CEP is not a new market. CEP is a very well know term in the BI parlance and there are already other players in this game. Some of the reasons for limited awareness / popularity of StreamInsight is that it involves a bit of .NET programming ( LINQ ), applications of this feature is limited and this is not something that masses would use frequently unlike other database development features or business intelligence related features. Also in my present knowledge there is no book available that is dedicated to StreamInsight.

In such cases when reference material available is scarce; articles on blogs and community websites, webcasts, technical presentation by experts, and video recordings of demonstrations are a very effective and economic way to fill up this reference material gap. Nick MacKechnie has shared an exhaustive resource collection of StreamInsight related webcasts, videos, presentations, hands on labs and demos. Though you might not be interested immediately to get your hands-on StreamInsight, but this page is definitely worthy of a place in your bookmarks section. Check out this StreamInsight resource collection from here.