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.

Update:

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

Comments (22)

  1. SSIS Stuff says:

    Be sure to check out Michael Entin’s recent blog post about the various options available for running

  2. Hi Michael.

    Thanks for you blog, learned a lot!

    Here is my challenge: I need to start a ssis package from a .net app. The package should run on the server.

    I tried the SQL Agent way but the call is asynchronous. I need a way to notify the user that the package execution is over. Any idea?

    Thanks,

    Olivier

  3. Andy says:

    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

  4. Davide Mauri says:

    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.

  5. Dan Kennedy says:

    Following on from my last blog post I had finally got an ASP.NET application to load an SSIS package from SQL Server MSDB storage, change some variables and execute it. All worked fine until I moved …

  6. v1rich says:

    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.

  7. A Blood says:

    Getting error DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER when running package from commandline using proper format and correct connection information… any ideas?

  8. EJ B Link says:

    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

  9. Agrapha says:

    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?

  10. Tim Januario says:

    Just an idea on how to do this that may be a bit easier than any of the methods covered.  IF you were to write a CLR procedure which accepts a string as its parameter.  The string passed in would be the xml definition of a package (either loaded from a source (file, server, etc)or from an application which is able to create the package definition).  Then using the CLR procecure that I mentioned above (which has references to the DTS runtime), you could use the Microsoft.SqlServer.Dts.Runtime.Package LoadFromXML() method to flesh out an empty package and then the Execute() method to actually run it.  Since you are calling the sproc on the sql server itself, as long as SSIS is installed, the package should run locally (which is in acutality remotely from the client).  I haven’t actually tried this, but it makes a lot of sense I think, and would be configurable only limited by the programmers imagination and time.  If you just need to run the same configuration remotely, this would be incredibly easy.  Any comments?

  11. michen says:

    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.

  12. Usuario says:

    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

  13. Ric says:

    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

  14. SSIS Stuff says:

    There are a lot of different ways to run SSIS packages , and many situations call for a custom package

  15. Kevin says:

    We are using the sp_start_job method but we would like to have the app know if the execution fails. Any suggestions? I don’t see a way to get a failed return code from sp_start_job. I can envision checking sysjobhistory but curious if there’s a better way. Thanks.

  16. Jane says:

    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

  17. Mike says:

    I want to execute a SSIS package via SQL Agent, I also want to use a SQL table (JobParameters) which will contain the parameters for the SSIS package.

    How can I select the parameters from this table and then pass them into the SSIS package from within a Job Step?

    You’ve stated that this is possible "… .You can’t pass parameters directly – it requires modification to the job, or some side-channel, e.g. config file or SQL table." However I’m not sure how to do it.

    Thanks

    Mike

  18. Rakesh says:

    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?

  19. If you execute SSIS packages from custom applications, you own the application and thus you are responsible for configuring .NET runtime properly to get the maximum performance. .NET configuration is usually performed using .exe.config files, so it is

  20. 深潭 says:

    本文详细讨论了,如何通过编程方式运行SSIS包!非常好,强烈推荐!收获很多!

  21. Dans Blog says:

    Following on from my last blog post I had finally got an ASP.NET application to load an SSIS package from SQL Server MSDB storage, change some variables and execute it. All worked fine until I moved …