4

I have a long-running SSIS package with many data flow tasks (see this question for more info on SSIS). Between these tasks, I'd like to send real-time status updates to a SignalR hub running in an intranet-facing ASP.NET MVC 4 app hosted in IIS 7.0, so users can watch the progress.

What is the best way to send status update messages to SignalR hub clients from SSIS?

The following SSIS tasks seem to be capable of delivering messages to outside systems:

  • SSIS Message Queue Task: Using an MSMQ private queue on the server sounds easy at first, except that MSMQ is a bit outdated, unmaintained, and lacking in documentation. There also isn't an obvious way to integrate with an existing SignalR-enabled web app. There is a write-up about a self-hosted SignalR hub with connections to MSMQ, but would this approach be possible inside an ASP.NET MVC app?

  • SSIS Execute SQL Task: I'm already doing this from SSIS (updating a status_id in a jobs table), so the hard part would be detecting these changes and routing them through the SignalR hub. SignalR already can use SQL Server for scaling out, but this technique assumes all messages are coming in through a SignalR hub to begin with, which is not the case here. Others talk about using SqlDependency, but could that enable an ASP.NET app to watch a DB table and be notified of all updates?

  • Use the SSIS Script task to send an HTTP request (C#): Upon reflection, this seems the most straightforward route, with a few caveats. The web application I'm building is NTLM-authenticated, so I'd need to use System.Net.WebRequest with authentication, which may or may not have issues within SSIS.

UPDATE regarding #3: the SSIS package is being run within SQL Server. The Windows account it's running under will fail authorization if I authenticate to the site using System.Net.CredentialCache.DefaultNetworkCredentials. So it seems WebRequest can only work in this scenario if it's possible to use alternate NTLM credentials (i.e. not derived from the account under which the package is running). The site does not accept basic HTTP authentication.

UPDATE 2: It looks like System.Net.NetworkCredentials can be instantiated with arbitrary domain/user/password and applied to WebRequest. I have yet to test this in production within an SSIS task. The question remains open because maintaining many identical SSIS Script tasks (or developing a reusable SSIS Task) is rather cumbersome. There may be an easier, faster, or more maintainable solution.

UPDATE 3 regarding bounty award: the 400-rep bounty will be awarded to the best answer that proposes a workable strategy and provides a few basic details at minimum.

Community
  • 1
  • 1
Roy Tinker
  • 10,044
  • 4
  • 41
  • 58
  • You are transferring data in a Data Flow Task, yeah? Is it basically an OLE DB Source to an OLE DB Destination for these packages? Your end goal is to know how many rows have moved from source to sink at the current moment in time, yeah? – billinkc Aug 30 '13 at 02:00
  • Also, how long is it taking to move millions (low, middle, high?) from one place to the other? That seems a fairly straight forward task and speedy operation unless you've got binary data in there or something that's going to clog up the pipes. – billinkc Aug 30 '13 at 02:03
  • I'm using SSIS data flow because it's the only straightforward route to transferring 20,000+ rows per second. There are several steps to the SSIS task (data flows, etc) -- the goal is to send updates to web browsers so they can keep tabs on the status. – Roy Tinker Aug 30 '13 at 19:50
  • Roy, I am looking at Option#3 from your list. Are you executing SSIS package out side of SQL Server Process? What if you run your entire SSIS package under one process account which will help to authenticate HTTPRequest with the help of System.Net.WebClient, this request sending will be with the help of ScriptTask and they will be executed after relevant Data-Flow tasks. – Hi10 Sep 04 '13 at 10:54
  • On Update#2, SSIS package will have event which can be used to trigger other task on success or error, so if event is handled and on script/custom task been called then there is reusability but true this will be within package :), but approach can be reused. – Hi10 Sep 04 '13 at 20:57
  • @Hi10 - yeah, this is all happening within a single control flow, so reusing script code is impossible without developing a signed SSIS Task... or, is it possible to reuse an event handler? – Roy Tinker Sep 04 '13 at 21:09
  • @Roy, long back in year 2006, used same approach to log the completion of task with custom message, that too with events. That why lets look at event handler, it will have option to call one task on many data flow task event. – Hi10 Sep 04 '13 at 21:22
  • Found this link to authorization when using SSRS http://social.msdn.microsoft.com/Forums/sqlserver/en-US/2d216af4-6586-4619-b8aa-56f05cbd8239/the-request-failed-with-http-status-401-unauthorized-in-ssrs it includes some links which I think might, if not provide a direct answer, at least help to understand the core issues. This video show how to call a web service and then shift data which I also found illuminating http://www.youtube.com/watch?v=_YSF6SR7fWU – Luke Baughan Sep 06 '13 at 13:10
  • did you get any final solutión about ***SSIS and SignalR***? Maybe with source code – Kiquenet Jan 12 '17 at 09:19
  • @Kiquenet: This was several years ago and I've moved on. If I recall correctly, the solution that worked was #3 - sending an HTTP request from SSIS to an ASP.NET MVC controller method, which then posted the message directly to the SignalR hub. – Roy Tinker Jan 12 '17 at 22:38

3 Answers3

2

SSIS Message Queue Task

If you use that you will have to post a message to a queue (MSMQ is a service with a file database). The message can be whatever you want. Then you have to write a windows service which polls messages in the MSMQ queue (or register to MSMQ events) and use a C# signalr client which transmit the message to the web clients.

+: MSMQ itself is reliable, MSMQ can post the message to other MSMQ
+: MSMQ will retain messages if they can not be delivered
-: All servers having MSMQ must be in the same Active Directory domain.
-: The service is not easy to write, if an error occurs in it all messages in the queue can be blocked (if the problematic message stays at the top of the queue)
-: It is not so easy to setup the security of MSMQ
-: There is no tool to recover the file db if it crashes.

My thought is you use MSMQ if your messages travel long distances through a local network / vpn, AND your messages must arrive and should never be lost.

In your case if a message does not arrive it is not catastrophic ...

Execute SQL Task

Don't update yourself the signalr db. This is not a reliable and long living solution.

Also don't use the watch feature on SQL table (it is called Service Broker). In my experience it is not reliable, it disconnects constantly, it refuses (re)connection for no reason, and the C# api is horrible. In fact you are notified of only one change,then you have yourself to find out the changes and register again for changes.

What you can do is use a CLR stored procedure including the signalr client. But if you have sql 2008R2 or less it have to be .NET 2.0, which is not supported by signalr (i'm pretty sure, but should be verified). ref: http://technet.microsoft.com/en-us/library/ms131103.aspx

Use the SSIS Script task to send an HTTP request (using NTLM)

HTTP request is the best way to go. You can put your code in a CLR stored procedure instead, so it will be reusable, and use a execute sql task. ref: http://technet.microsoft.com/en-us/library/ms190790(v=sql.105).aspx

Softlion
  • 12,281
  • 11
  • 58
  • 88
  • 1
    Bounty awarded. Thanks for providing helpful information on the benefits and drawbacks of each approach, and for the great suggestion to put the HTTP request in a CLR stored procedure. Cheers! – Roy Tinker Sep 10 '13 at 18:58
1

I think that you could use the SQL Server service Broker for that purpose.

Furthemore somebody seems to have achieved something similar: Pushing data from SQL Server to Web Application with SignalR

Community
  • 1
  • 1
Fabien
  • 1,015
  • 2
  • 11
  • 22
  • Good solution ... but dangerous. Can it really evolve ? Do you trust the .NET 2 code will always be compatible with the .NET 4 server code ? This is an "open source like" lib, and i won't trust this solution myself. open source people often don't like to maintain "old technology" versions. – Softlion Sep 10 '13 at 22:06
1

I would simply adopt #3 with ntlmaps installed locally (a proxy server that enables custom NTML credentials) and properly defined network settings in the configuration file.

Giacomo Tesio
  • 7,144
  • 3
  • 31
  • 48