Pushing messages from SSIS (2012) to a SignalR hub in an ASP.NET web application - what's the best approach?

Question

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

What is the best way to send update status messages for SignalR Hub Clients from SSIS?

The following SSIS tasks appear to be able to deliver messages to external systems:

• The SSIS message queue task: first, using the private MSMQ queue on the server sounds simple, except that MSMQ is a bit dated, not received, and does not have documentation. There is also no obvious way to integrate with an existing web application that supports SignalR. There is a record for a self-service SignalR hub with connections to MSMQ , but will this approach be possible inside an ASP.NET MVC application?

• SSIS SQL task execution: I am already doing this from SSIS (updating status_id in the task table), so the hard part will detect these changes and route them through the SignalR hub. SignalR can already use SQL Server for scaling , but this method assumes that all messages arrive through the SignalR hub for starters, which is not the case here. Others talk about using SqlDependency , but can this allow the ASP.NET application to view the database table and receive notifications of all updates?

• Use the SSIS Script task to send an HTTP request (C #): when reflected, this seems the easiest way with a few caveats. The web application I create is authenticated by NTLM, so I will need to use System.Net.WebRequest with authentication, which may or may not have problems in SSIS.

UPDATE on # 3: The SSIS package runs on SQL Server. The Windows account on which it is running is not authorized if I authenticate to the site using System.Net.CredentialCache.DefaultNetworkCredentials . Thus, WebRequest can only work in this scenario, if it is possible to use alternative NTLM credentials (i.e., it is not displayed from the account under which the package works). The site does not accept basic HTTP authentication.

UPDATE 2: it looks like System.Net.NetworkCredentials can be created with an arbitrary domain / user / password and applied to WebRequest. I have yet to test this in production as part of the SSIS task. The question remains open, since saving many identical SSIS Script tasks (or developing a reusable SSIS task) is rather cumbersome. Could be a simpler, faster or more maintainable solution.

UPDATE 3 in relation to reward reward: A reward for 400 reprisals will be awarded to the best answer that offers an effective strategy and provides minimal minimum details.

Answer 1

SSIS Message Queue Task

If you use this, you will need to send a message to the queue (MSMQ is a file database service). The message can be anything you want. Then you need to write a Windows service that checks messages in the MSMQ queue (or logs in MSMQ events) and uses the C # signalr client, which sends the message to 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 that you are using MSMQ if your messages travel long distances through the / vpn LAN, And your messages should arrive and should never be lost.

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

SQL task execution

Do not update signalr db itself. This is not a reliable and long-lasting solution.

Also, do not use the view function in the 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 C # api is terrible. In fact, you receive a notification of only one change, then you must learn the changes yourself and re-register the changes.

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

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

An HTTP request is the best way. Instead, you can put your code in a CLR stored procedure, so it will be reused and use the sql task execution. ref: http://technet.microsoft.com/en-us/library/ms190790(v=sql.105).aspx

Answer 2

I think you can use SQL Server Service Broker for this purpose.

It looks like someone has achieved something similar: Pushing data from SQL Server to a web application using SignalR

Answer 3

I would just accept # 3 with ntlmaps installed locally (a proxy server that allows you to configure NTML credentials) and the network settings in the configuration file are correctly defined.