Partager via

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

Comments

  • Anonymous
    March 23, 2007
    Be sure to check out Michael Entin's recent blog post about the various options available for running

  • Anonymous
    April 06, 2007
    The comment has been removed

  • Anonymous
    April 26, 2007
    A variation on idea 5, you could use the Service Broker functionality to control when the job is initiated. That could also be used to send a message back to the user reporting progress and completion. http://www.microsoft.com/technet/technetmag/issues/2005/05/ServiceBroker/default.aspx

  • Anonymous
    May 21, 2007
    I've created a tool that mimics the DTExec behaviour, extending the logging functionalities. I've shared the source code on codeplex:http://www.codeplex.com/DTLoggedExec. If someone wants to implement the options #5 i think it can find the source code useful.

  • Anonymous
    June 10, 2007
    The comment has been removed

  • Anonymous
    June 13, 2007
    The SQL Agent example provided by microsoft has a bug, that is it will always return "package succeeded", because sp_start_job always returns success if the job is started successfully.  To get the real return status, from this forum: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1622660&SiteID=1 here is what you have to do: To get the return status, you would need to use a combination of sp_start_job, a loop that repeatedly calls sp_help_job and a delay until the job finishes, and then sp_help_jobhistory to get the result.

  • Anonymous
    June 22, 2007
    Getting error DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER when running package from commandline using proper format and correct connection information... any ideas?

  • Anonymous
    June 24, 2007
    I am using #1 now and impersonation is on. But, I can't run the ETL unless I have local administrator privileges for the impersonated user. Any ideas how to solve this? What are the minimum rights

  • Anonymous
    July 31, 2007
    I need help. My SSIS package should be simple. Select from a datawarehouse and load into a sqlserver table. I have built and rebuilt this package 100+ times and always get it to fail. Currently I too am getting DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER . I have encrypted a package password and decrypted on the server. My fear is this: Am I unable to run a SSIS package on one server which truncates and reloads a different server? Can I not build this package on my workstation and deploy it to the SSIS server so it can reload data on a seperate SQLServer?

  • Anonymous
    August 15, 2007
    The comment has been removed

  • Anonymous
    August 15, 2007
    To Tim: I would not recommend running SSIS packages inside SQL Server using SQL/CLR. SQL Server has strict requirements on the type of code running inside (this is how it achieves the great reliability, and why by default it only allows "safe" .NET code). The SSIS uses "unsafe" .NET code, and we've not done enough testing of SSIS and dependencies inside SQL Server to recommend running it this way, so it is unsupported.

  • Anonymous
    September 05, 2007
    I have a Problem: I have a code that generates a package. The package obtains data of a base gives data and it happens to another one. but in he himself server The code is written in VB .net When I generate the package gives the following error me: OnError,USERS,EMPRESABUENAuser,DataFlow,{78568468-D278-42DE-94CE-5F38FBCE8075},{CED37B8C-56BF-4AFD-9F2E-758EF7677836},05/09/2007 08:05:38 a.m.,05/09/2007 08:05:38 a.m.,-1071611876,0x,SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "HERASQL2005.LD_COM_CCC2" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed. thanks!!! help me please

  • Anonymous
    September 11, 2007
    I have a package that I want to run on a different computer using dtexec. What is the minimum software I need to install on it? At the moment it does not recognise the package extension. [M.E.] You have to install SSIS on the machine where package runs. Also check these posts: http://blogs.msdn.com/michen/archive/2006/08/11/package-exec-location.aspx and http://blogs.msdn.com/michen/archive/2006/11/11/ssis-product-level-is-insufficient.aspx

  • Anonymous
    September 14, 2007
    There are a lot of different ways to run SSIS packages , and many situations call for a custom package

  • Anonymous
    September 26, 2007
    The comment has been removed

  • Anonymous
    October 21, 2007
    Is it possible to run an SSIS from vbscript?  At the moment, I call several DTSs programmatically, using 'run dts': but how does this translate to SSIS on upgrade? Thanks Jane

  • Anonymous
    October 31, 2007
    The comment has been removed

  • Anonymous
    November 04, 2007
    is it possivle to deploy a package programmatically? we have an application which have a work flow for approval of object. if the object(ssis package) is approved by the concerned authority it has to be deployed to sql server. How can this code be integrated into the application?

  • Anonymous
    March 14, 2008
    The comment has been removed

  • Anonymous
    April 07, 2008
    本文详细讨论了，如何通过编程方式运行SSIS包！非常好，强烈推荐！收获很多！

  • Anonymous
    April 15, 2008
    The comment has been removed

  • Anonymous
    May 25, 2009
    来自 Micheal Entin: http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx