Where is my SQL Server Integration Services package running?


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:
http://support.microsoft.com/kb/918760

Comments (11)

  1. Martin says:

    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….

  2. The error tells you that you have not installed SSIS on the machine where the package is running – probably you’ve only installed SQL Server and Workstation Components. Another possible reason is that the package uses components that require higher edition

  3. Leonard says:

    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.

  4. 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

  5. Leonard says:

    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.

  6. Karthik says:

    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

  7. Dwaraka says:

    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

  8. >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.

  9. steve says:

    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.

  10. Steve says:

    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?

  11. 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)