Enable SSIS Stored Procedure at SQL Server Startup

## Enable automatic execution of Integration Services stored procedure at SQL Server startup

> **Question**
>
> *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?*
>
> --- [Source: Enable catalog.startup autoexecution for SSISDB after creation](https://www.sqlservercentral.com/forums/topic/enable-catalog-startup-autoexecution-for-ssisdb-after-creation)

**Answer**

To see if the checkbox was checked:

```sql
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:

```sql
exec sp_procoption
    @ProcName = 'sp_ssis_startup',
    @OptionName = 'startup',
    @OptionValue = 1;
```