Partilhar via

Where is my SQL Server Integration Services package running?

  • Artigo

By 'where package is running' I mean what CPU and memory resources are used, and where the SSIS needs to be installed. This simple question surprisingly often confuses users, as there are many options to store packages and many options to run it.

The answer is very short - inside the application that started it. SSIS 2005 runtime is in-process object model; to use it you need some process that loads and executes it, the most common hosts are DTEXEC (also used when you create SSIS step in Agent) and DTEXECUI tools. Unless you explicitly call some remote agent that runs package somewhere else - the package runs locally.

So:

  • If you run package using DTEXEC or DTEXECUI (Execute Package Utility) - it runs on your workstation, under your account.
  • If you run package using BI Dev Studio (BIDS) - also runs on your workstartion, under your account.
  • If you run package using SQL Management Studio (SSMS) - again SSMS directly runs it on your workstartion.
  • If you execute package programmatically using SSIS API, it runs inside your application.
  • If you schedule it using SQL Agent - the Agent runs DTEXEC, so it runs on Agent server, under Agent service account or Proxy Account if you configured one.

BTW, this simple answer implies the location where the package is stored does not matter at all - you can store the package on remote machine, but when you run it using DTEXEC or start package from SQL Server Management Studio, the package runs on your machine where DTEXEC or SSMS runs.

Implications: Besides consuming CPU and memory resources, you also need to install SSIS on the machine where the package runs.

Note: of course, besides the CPU and memory used by the package process itself, it may consume other resources: e.g. Execute Process task may start new process(es); Execute SQL task can send a query to remote SQL server, thus consuming resources of this server while it executes this query; FTP task consumes some resources of remote FTP server; etc.

---

This leads us to the second question: if I want to run my package on a remote machine, without installing SSIS locally - how can I do it? There are many options, the most common are (1) use SQL Agent, (2) create a custom Web service application. To use Agent you need to create a job on the machine where you want to run the package, configure the job to use the appropriate proxy account, create a step that executes the package. Now you can either schedule the job, or start it manually from SQL Server Management Studio, or start it programmatically by executing sp_start_job stored procedure.

Hint: if you have problems with the package executing under Agent, consult this KB article:
https://support.microsoft.com/kb/918760

Comments

  • Anonymous
    November 09, 2006
    Any idea why MS did not design a remote initiate function for SSIS packages to run on the SSIS server?  They had to realize the user community has various methods to manage scheduled tasks, not just SQL agent....

  • Anonymous
    November 11, 2006
    The comment has been removed

  • Anonymous
    March 16, 2007
    The process seems to be lacking in intuitiveness. It's often easier to write a script and save it then run it as a query than navigate the mine field. If I have a package stored on a SQL Server (Enterprise Edition) that I can see in SQL Server Management Studion via an SSIS connection/stored packages/MSDB, should it not run on the server? If it does not run on the server, it hardly seems "Integrated". In fact, it seems the word "Distributed" would be more accurate. Do I really have to write an ASP.net app just to run a package on the server? That seems like an extremely poor design.

  • Anonymous
    March 22, 2007
    To Leonard: You don't have to write an ASP.NET app, there are lots of other options, most common is using SQL Agent. Also, see this entry: http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

  • Anonymous
    May 04, 2007
    michen: Thanks for the response. Wouldn't a "Run From Server" option in Management Studio be a better solution? Then you could right-click on the package and choose "Run From Server" instead of creating a job to run a package.

  • Anonymous
    May 21, 2007
    Hi SIR, How do i call the SSIS Stored Packages[File System] in my Stored Procedure. Can u give me the syntax for the same. THanks In advance! Karthik

  • Anonymous
    July 24, 2007
    I'm calling SSIS package in my stored procedure and executing the stored proce from a windows application. Where does the SSIS package run in this case? Any help is appreciated Thanks Dwaraka hdwarka@yahoo.com

  • Anonymous
    July 24, 2007
    >I'm calling SSIS package in my stored procedure It obviously depends on how exactly the stored procedure is calling the package. Do you use Sql Agent, xp_cmdshell? Anyway, I think this blog with some common sense should give you the answer. Just realize that there is no magic here - e.g. if the stored procedure is calling SSIS, there is no way for SSIS to know who started the stored proc. So at least your windows application that executed the stored proc can be removed from picture completely.

  • Anonymous
    July 25, 2007
    i agree, microsoft should provide "Run From Server" option in Management Studio. often a process needs not to be run on a scheduled basis and DBAs should be able to start a process from their workstation. i'll open a request to MS.

  • Anonymous
    August 29, 2007
    Never mind where it is running, where is SSIS in SQL Server?  In 2000 DTS was in Enterprise Manager.  Where is it in SQL 2005?

  • Anonymous
    August 29, 2007
    To Steve: as always, SQL Books Online is the best place to start: http://msdn2.microsoft.com/en-us/library/ms141178.aspx The designer lives in Business Intelligence Development Studio (sometimes refered to as BIDS)