The SSIS subsystem failed to load
When this error is received the initial response is to look into SQL Server Integration Services, however this error can occur on systems even when no SSIS is installed. The subsystems are actually related to the SQL Server Agent. Subsystems are predefined objects that present a set of functionality to the SQL Server Agent proxy. This enables SQL Server agent to run SSIS packages with SSIS being installed. The SQL Server Integration Services subsystem helps provide the SSIS functionality to support SQL Server Maintenance plans.
Generally the SSIS subsystem error is discovered after a SQL Server instance migration. After creating a new maintenance plan the established jobs will sometimes fail to run with error indicating that the SSIS subsystem is to blame. While this can seem like it is going to be a huge undertaking to resolve, it turns out that it is actually quite simple.
- Take a backup of your system databases There’s no time better than right before we start modifying system tables to take some backups! This ensures that if there are additional problems created we can easily recover.
- Check to see the existence of subsystem values SELECT * FROM msdb.dbo.syssubsystems
- Delete all the existing values DELETE FROM msdb.dbo.syssubsystems
- Repopulate the subsystems EXEC msdb.dbo.sp_verify_subsystems 1
- Restart SQL Server Agent
Once the SQL Server Agent has been restarted, try running those jobs again. This time the job should run and execute successfully. If there is still an issue with the subsystem failing at this point it could be an NTFS file permissions issue. The DTSbinn in the SQL Server instance root directory should allow access to the account(s) running the SQL Server Instance and the SQL Server Agent. If both of these services are unable to access the DTSbinn directory then this will also cause the SSIS subsystem failed to load error.