Running SSIS package programmatically

from Michael Entin's notebook,thanks for his contribution

Running SSIS package programmatically

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: http://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: http://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 http://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: http://msdn2.microsoft.com/en-us/library/ms403355.aspx. Also make sure you implement logging as described in this KB http://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: http://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.