Running SSIS package programmatically

  • Article

I got several questions asking what is the best way to run SSIS packages programmatically. One question is about running SSIS from a .NET 1.1 application (SSIS uses .NET 2.0). Another about running package remotely "Do I really have to write an ASP.net app just to run a package on the server?" There were also questions about running package from ASP.NET page (which second user tries to avoid, but surprisingly many people want).

Let's review what options are available, and discuss which is most appropriate for each case.

 

1. Run package programmatically using SSIS Object Model. This is discussed in details in Books Online here: https://msdn2.microsoft.com/en-us/library/ms136090.aspx

Benefits: everything runs in process, it is very easy to set variables or modify package before executing it. You can also get events about package progress or ask it to stop by setting CancelEvent.

Drawbacks: Obviously this is local execution - you need to install SSIS on same machine where your app runs. This method also can't be used from .NET 1.1 application, unless it is moved to .NET 2.0 (which should be very easy to do, and in my experience improves the performance as well).

ASP.NET specific: the impersonation context does not get passed to additional threads SSIS package creates, so the data source connections will not be impersonated. Also, ASP.NET can be configured to recycle the worker process in case it consumes too much memory to improve availability of ASP.NET application. Since SSIS is likely to consume a lot of memory if you have lots of data, it can trigger this recycling and lower reliability of your application.

 

2. Start DTEXEC.EXE process. DTEXEC is command line utility for executing SSIS packages. See its command line options here: https://msdn2.microsoft.com/en-us/library/ms162810.aspx

Benefits: running package out of process gains reliability. Can be used from any programming language (including .NET 1.1 :)). Easy to pass parameters by setting variables values.

Drawbacks: Also local only. Harder to get information about package progress (but SSIS logging can give you most functionality). Some overhead on starting new process (likely minimal compared to execution time for big packages).

ASP.NET specific: Win32 CreateProcess function ignores the thread impersonation. So if you want DTEXEC to run under account different from ASP.NET process account, you should either make user enter name/password and pass it to Process.Start, or use method described in the following KB to run child process under impersonated account https://support.microsoft.com/kb/889251.

 

3. Use SQL Agent. You can configure an Agent job to run your package (either do it manually in advance if the package is static, or programmatically using SMO or using SQL stored procedures just before running the package), and then start it programmatically using SMO or sp_start_job.

MSDN has sample of calling sp_start_job from managed code: https://msdn2.microsoft.com/en-us/library/ms403355.aspx. Also make sure you implement logging as described in this KB https://support.microsoft.com/kb/918760 so if anything goes wrong you could troubleshoot it. This article also describes what can go wrong with the package when scheduled in Agent.

Benefits: You get remote package execution. You get execution serialization (only one instance of a job runs at a time). You can run the package under any account (use Agent proxy).

Drawbacks: Agent requires installation of SQL Server engine. You can't pass parameters directly - it requires modification to the job, or some side-channel, e.g. config file or SQL table.

 

4. Use some other utility to start DTEXEC for you.

Of course, you can use any other generic task scheduler instead of SQL Agent, if you don't want to use Agent for some reason. If you have some scheduler or remote execution infrastructure already in place - use it. All you need is a tool that can start an executable (DTEXEC) and pass command line arguments.

 

5. Create a custom application that will run the package (either using OM as described in method #1, or using DTEXEC as in method #2). Expose it as a web service or DCOM class, call this service from your program.

MSDN has sample code for both the web service and its client: https://msdn2.microsoft.com/en-us/library/ms403355.aspx (second part of this page).

Benefits: Easy to add custom logic. You get remote package execution. Easy to pass parameters.

Drawbacks: Need to write code.

 

6. Invent your own - I probably missed several ways. Please post comments with any ideas.

 

Now you should be able to answer the questions in the beginning of this blog:

  • .NET 1.1 app - #2 is probably the easiest way to do it.
  • Running package remotely: Agent (#3) is likely the simplest way.
  • ASP.NET app - Again, #3 is usually the best and most reliable, as it allows you to configure the package to run as a user different from account of ASP.NET process, and you can install SQL and run SSIS packages on a separate box to isolate it from web server even more.

Update:

Excellent option for running SSIS from scripts: PowerShell. Read more at https://blogs.msdn.com/b/dbrowne/archive/2010/10/11/remote-ssis-package-execution-with-powershell-2-0.aspx