Enable catalog.startup autoexecution for SSISDB after creation

• istvanszucs3

  Mr or Mrs. 500

  Points: 513

  More actions

  May 31, 2014 at 10:33 am

  #396841

  There is a checkbox when you create an SSIS catalog which says "Enable automatic execution of Integration Services stored procedure at SQL Server startup". It's a good practice to have this option on. BUT! How do you verify whether this option is ON or OFF after you (or someone else) have created a catalog and how do you enable it if it wasn't enabled at the time of creating the catalog?

  Thank you for the suggestions in advance!

• kgeeraerts

  SSC-Addicted

  Points: 443

  More actions

  August 7, 2014 at 6:57 am

  #1735946

  To see if the checkbox was checked:

  SELECT name, is_auto_executed FROM sys.procedures WHERE name = 'sp_ssis_startup'

  If 'is_auto_executed' is 1 then it was checked at installation.

  To change the value of 'is_auto_executed' to 1 when it wasn't enabled at installation:

  EXEC sp_procoption

  @ProcName = 'sp_ssis_startup',

  @OptionName = 'startup',

  @OptionValue = 1;

  This should answer your question.

  Greetz,

  Ken