Nov 042009
 

Way too often, we hear many misconceptions about what the Integration Services Service actually does. Some think it is required to execute packages. Others think it is used for checkpoint restarts. Others think it speeds up execution. So, which group is correct?

The answer is none of them. The SSIS Service, quite simply, is responsible for managing the Integration Services interface in SQL Server Management Studio. It enables the ability to import/export packages, view running packages, and view stored packages. It really doesn’t do anything more than that.

Disabling the service will not affect:

  • package development (you can develop packages without the service)
  • the ability to execute packages (DTEXEC and other executables are responsible for this)
  • checkpoint restarts of failed SSIS packages
  • the ability for users to query the msdb database for stored packages via SQL
  • package execution speed
  • SQL Server Agent’s ability to execute packages

The SSIS Service is not cluster-aware, and is in fact not recommended. http://msdn.microsoft.com/en-us/library/ms345193.aspx

EDIT – See Michael Entin’s comment below. The SSIS Service will also cache component/task metadata so that the SSIS runtime engine can poll the cache to see what is installed, which may help speed up package load times however small those gains may be.

  7 Responses to “SSIS – What does the SSIS Service actually do, anyway?”

  1. There is another function – cache component metadata information (i.e. list of installed tasks, transforms, etc) – if it is enabled, SSIS runtime uses the cache to get this list rather than enumerate it when needed. But usually it is very small gain, unless you execute a lot of very small packages. It may speed up the SSIS designer launch though.

  2. Does this mean that even DTEXEC’s loading of packages stored in SQL server is still done via MSDB stored procs, and not via the service interface?

  3. That’s correct – the service has nothing to do with package execution/loading.

  4. Is there any site where i can explore all the features of SSIS?

  5. קידום דפי תוכן אינטרנטיים עושים רק בחברת קידום אתרים מקצועית כמו חברת נט-סטייל . צרו עמנו קשר על מנת לרכוש שרות של קידום אתרים בגוגל בדרך הטובה ביותר.

  6. Regarding piers7′s question I was wondering about this too. If you are using the /DTS option (or Package Source = SSIS Package Store in the Agent Job UI) then the SSIS service must be running. If you are using /SQL (Package Source = SQL Server) then the SSIS service doesn’t need to be running.

  7. I’ve doing some testing, and if I install the SQL Server database engine only, without the SSIS service installed, my packages fail. The SSIS service doesn’t execute the packages, but somehow it’s presence is still required?
    I also found that if the service is installed and not running, it is started once you execute a package.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>