0

I need to launch a monthly process to create a preformatted Text file. The data sources are in Database tables (sql server 2008 R2). It's necessary to perform some logic (ETL) on the data and then write the txt file.

I found 2 solutions.

First is: use a scheduled SSIS where i Extract, Trasform data with logic inside a Script Task (c#) and dump the results in the txt file

Second solution is: I could use SSIS with a script task only to call an action MVC (c# code and use WebClient.UploadData). Data Extraction, transformation and results dumping in a txt file is totally performed in the MVC action. I think that it should be better move the logic in a MVC action for the following pros:

  • Test Driven Development

  • Logging (with log4net)

  • Entity Framework

  • Reuse code logic of the WebApp

  • The future sql server migration could be simpler (dtsx migration) since the logic is outside the SSIS

I am more confident if all the application logic is in the WebApp: i prefer using SSIS only for huge ETL.

The Cons of the second solution is that it is necessary perform a security management of the MVC action call to avoid malicious calls.

QUESTION

What do you think is the best solution?

Logic inside or outside (WebApp MVC) SSIS?

jebbo
  • 53
  • 3
  • 14
  • Option one is complicated. Option two is very complicated. Build it in whatever tool you can support. I suggest you don't use SSIS at all. Just use some other tool to execute your web app directly on a scheduled basis. From windows scheduler you could use CURL.EXE (http://stackoverflow.com/questions/12222607/how-to-do-a-soap-wsdl-web-services-call-from-the-command-line) or a VBScript (http://axwonders.blogspot.com.au/2011/02/schedule-windows-task-to-call-aspnet.html) – Nick.Mc Apr 13 '16 at 02:16
  • Or you could use something like this http://www.codeproject.com/Articles/12117/Simulate-a-Windows-Service-using-ASP-NET-to-run-sc to build your scheduler inside IIS. – Nick.Mc Apr 13 '16 at 02:17
  • Don't assume that because you are doing data extracts that you **have** to use SSIS. It comes with it's own complications. For example you need a special and suprisingly difficult tool (SSDT) to build a package in the first place. Just build it all in the tool your comfortable with (ASP.Net) then use windows scheduler to call it when required. – Nick.Mc Apr 13 '16 at 02:18
  • Thank you @Nick.McDermaid, i prefer use SSIS like launcher because i want manage a list of scheduled processes. Then i need to put SSIS under a version control (TFS). With a windows scheduler i loose this advantages. – jebbo Apr 14 '16 at 06:32
  • You can put any script or file under version control. For the curl example, you write all the actual logic in a web service (which is in version control) and you call it using curl (using a script, also under version control), and if you like you can script out your windows scheduler definition and put that under version control also. All I'm getting at is if you have a SSIS package and all it contains is a script task then SSIS is the wrong tool – Nick.Mc Apr 14 '16 at 09:33

2 Answers2

0

I am confronted with the same. I choose to put the logic in an ASP.NET web app for the same reasons you listed.

Chris Harrington
  • 1,238
  • 2
  • 15
  • 28
0

My recommendation is to look into using hangfire.io. I think it is designed for such scenarios. That being said you can always use the SSIS to call the MVC action. For security reason I would suggest making it a post request over https and provide some type of authentication. If it is a long running process you might have to extend the webClient to extend time. Credit for following code goes to how-to-change-the-timeout-on-a-net-webclient-object . 

public class LocalWebClient : WebClient
{
    protected override WebRequest GetWebRequest(Uri uri)
    {
        WebRequest w = base.GetWebRequest(uri);
        w.Timeout = 10 * 60 * 1000;//10 minutes
        return w;
    }
}

usage: using (var client = new LocalWebClient()) {}

salli
  • 722
  • 5
  • 10