Maintenace tasks started failing | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Maintenace tasks started failing

SQL 2005 maintenace jobs started failing due to the following error: the SSIS subsystem failed to load The jobs are now all suspended. When I check the sql agent subsystems SSIS is not loaded. These jobs were working last week. The change made prior to this failure is that I made the sql server listen on a static port vs being dynamic. The other server that I did this on still works fine. Thanks in advance for your help!
What is the service pack level on SQL Server?
SP2 has relevant fixes to the maintenance plans and SSIS issues. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Service Pack 2 with a hotfix. Also, this is a clustered environment with an instance of sql server 2000 EE sp4 + hotfix on each node as well as the instance of sql 2005 Std sp2 +update (using mount point drives). There are 2 installations as described, on for testing and the other for production. I did not install SSIS in either of the 2 environments described previously, one of which still works (testing) and the one that is broken (production). Would installing SSIS correct this issue? If the maintenance jobs need SSIS though it should never have worked in the first place…. Any insight into this would be appreciated. Thanks!
Ha, in the cluster environment then you need to have SSIS installed on both the nodes.
Referhttp://support.microsoft.com/kb/918760 for more information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
That still doesn’t answer my question about why it worked in the first place. The error did not start until I changed the sql server to a static port. Not that I think a static port would cause the problem. It was also still working on another machine (testing) without SSIS installed on either node. I just installed SSIS on the testing cluster (both nodes) and will schedule the install on the production cluster during the maintenance window.
Also by default SQLServer Integration Services is not cluster aware !
you should make a clusterresource yourself !
HOWTO :http://msdn2.microsoft.com/en-us/library/ms345193.aspx
:http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx
As previously if the server was configured to listen on dynamic ports the connection was succesful, as far as other instance is concerned it was unable to connect using last port (when it was in dynamic). As the other one changed to static in general it takes bit time to refresh the DNS in order to identify the correct port to connect, this is based on my assumption on usage of dynamic and static ports. If you need further explanation I would need to dig out further on SSIS bit. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>