Troubleshooting the SSIS Service Start-up Failure issue
I ran into an interesting situation when one of my customers reported an issue with SSIS service unable to start. The Error message that was displayed when he was attempting to start the start didn’t reveal much information.
When I checked the Event viewer logs, I found the following error message corresponding to the Start-up failure of SSIS Service.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7000
Date: 11/13/2010
Time: 6:24:29 AM
User: N/A
Computer: Machine2003
Description:
The SQL Server Integration Services service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.
----------------------------------------------------------------------------------
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7009
Date: 11/13/2010
Time: 6:24:29 AM
User: N/A
Computer: Machine2003
Description:
Timeout (30000 milliseconds) waiting for the SQL Server Integration Services service to connect.
To get started with the troubleshooting , at first I enabled the Sqldumper.exe utility to generate dump files by following this article: https://support.microsoft.com/kb/941151 This article elaborates on how to enable the SSIS related process to internally call the Sqldumper.exe utility to generate a dump file.
The Sqldumper.exe utility generates the dump file in the "%ProgramFiles%\Microsoft SQL Server\90\Shared\ErrorDumps" folder.
To start the collection of the dumps, we attempted to start the service once again. This generated the dump file in the above specified log location.
So this is how my dump analysis looked like… The very first thing I tried do was to DumpStackObjects using the (dso) command
0:000> !dso
OS Thread Id: 0x1b34 (0)
ESP/REG Object Name
0027ef90 050a7380 System.Configuration.ConfigurationErrorsException
0027ef94 010bec3c System.Configuration.ClientConfigurationSystem
0027efd8 050a7380 System.Configuration.ConfigurationErrorsException
0027efe8 050a7094 System.Configuration.ConfigurationErrorsException
0027efec 050a7380 System.Configuration.ConfigurationErrorsException
0027eff0 010bec3c System.Configuration.ClientConfigurationSystem
0027eff8 050a7094 System.Configuration.ConfigurationErrorsException
0027f000 050a7380 System.Configuration.ConfigurationErrorsException
0027f008 010bec3c System.Configuration.ClientConfigurationSystem
0027f0f8 010bec3c System.Configuration.ClientConfigurationSystem
0027f0fc 010bec3c System.Configuration.ClientConfigurationSystem
0027f118 010bec04 System.String system.diagnostics
0027f11c 010bec04 System.String system.diagnostics
0027f120 010bec3c System.Configuration.ClientConfigurationSystem
0027f130 010bec3c System.Configuration.ClientConfigurationSystem
0027f140 010bec04 System.String system.diagnostics
0027f150 010bec04 System.String system.diagnostics
0027f160 010be7f0 System.Diagnostics.TraceSwitch
0027f16c 010be7f0 System.Diagnostics.TraceSwitch
0027f178 010bebf8 System.Object
0027f194 010be7f0 System.Diagnostics.TraceSwitch
0027f198 010be8f4 System.String InitializeComSecurity: Enter
0027f1ac 010be7f0 System.Diagnostics.TraceSwitch
0027f1c4 010be7f0 System.Diagnostics.TraceSwitch
0027f1e0 010be7f0 System.Diagnostics.TraceSwitch
0027f1e4 010be8f4 System.String InitializeComSecurity: Enter
0027f1f0 010be8f4 System.String InitializeComSecurity: Enter
0027f200 010be7f0 System.Diagnostics.TraceSwitch
0027f20c 010be9dc System.Object[] (System.Object[])
Proceeding further tried to dump the Object (do) 050a7380
0:000> !do 050a7380
Name: System.Configuration.ConfigurationErrorsException
MethodTable: 708f6b18
EEClass: 708c27dc
Size: 92(0x5c) bytes
GC Generation: 0
(C:\Windows\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll)
Fields:
MT Field Offset Type VT Attr Value Name
6ec10b24 40000b5 4 System.String 0 instance 00000000 _className
6ec0ff98 40000b6 8 ...ection.MethodBase 0 instance 00000000 _exceptionMethod
6ec10b24 40000b7 c System.String 0 instance 00000000 _exceptionMethodString
6ec10b24 40000b8 10 System.String 0 instance 050a742c _message
6ec0a480 40000b9 14 ...tions.IDictionary 0 instance 00000000 _data
6ec10cb8 40000ba 18 System.Exception 0 instance 050a7094 _innerException
6ec10b24 40000bb 1c System.String 0 instance 00000000 _helpURL
6ec10740 40000bc 20 System.Object 0 instance 050a7628 _stackTrace
6ec10b24 40000bd 24 System.String 0 instance 00000000 _stackTraceString
6ec10b24 40000be 28 System.String 0 instance 00000000 _remoteStackTraceString
6ec12d70 40000bf 34 System.Int32 1 instance 0 _remoteStackIndex
6ec10740 40000c0 2c System.Object 0 instance 00000000 _dynamicMethods
6ec12d70 40000c1 38 System.Int32 1 instance -2146232062 _HResult
6ec10b24 40000c2 30 System.String 0 instance 00000000 _source
6ec133ec 40000c3 3c System.IntPtr 1 instance 0 _xptrs
6ec12d70 40000c4 40 System.Int32 1 instance -532459699 _xcode
6ec10b24 40030e4 44 System.String 0 instance 00000000 _filename
6ec12d70 40030e5 48 System.Int32 1 instance 0 _line
6ec10b24 4000253 4c System.String 0 instance 00000000 _firstFilename
6ec12d70 4000254 54 System.Int32 1 instance 0 _firstLine
6ebe42f4 4000255 50 System.Object[] 0 instance 00000000 _errors
Moving on, I tried to view what the exception message had to say:
0:000> !do 050a742c
Name: System.String
MethodTable: 6ec10b24
EEClass: 6e9cd65c
Size: 100(0x64) bytes
GC Generation: 0
(C:\Windows\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll)
String: Configuration system failed to initialize
Fields:
MT Field Offset Type VT Attr Value Name
6ec12d70 4000096 4 System.Int32 1 instance 42 m_arrayLength
6ec12d70 4000097 8 System.Int32 1 instance 41 m_stringLength
6ec11804 4000098 c System.Char 1 instance 43 m_firstChar
6ec10b24 4000099 10 System.String 0 shared static Empty
>> Domain:Value 0034b260:050601d0 <<
6ec11754 400009a 14 System.Char[] 0 shared static WhitespaceChars
>> Domain:Value 0034b260:05060a7c <<
Next , I tried to access the inner exception
0:000> !do 050a7094
Name: System.Configuration.ConfigurationErrorsException
MethodTable: 708f6b18
EEClass: 708c27dc
Size: 92(0x5c) bytes
GC Generation: 0
(C:\Windows\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll)
Fields:
MT Field Offset Type VT Attr Value Name
6ec10b24 40000b5 4 System.String 0 instance 00000000 _className
6ec0ff98 40000b6 8 ...ection.MethodBase 0 instance 00000000 _exceptionMethod
6ec10b24 40000b7 c System.String 0 instance 00000000 _exceptionMethodString
6ec10b24 40000b8 10 System.String 0 instance 050a633c _message
6ec0a480 40000b9 14 ...tions.IDictionary 0 instance 00000000 _data
6ec10cb8 40000ba 18 System.Exception 0 instance 00000000 _innerException
6ec10b24 40000bb 1c System.String 0 instance 00000000 _helpURL
6ec10740 40000bc 20 System.Object 0 instance 050a7314 _stackTrace
6ec10b24 40000bd 24 System.String 0 instance 00000000 _stackTraceString
6ec10b24 40000be 28 System.String 0 instance 00000000 _remoteStackTraceString
6ec12d70 40000bf 34 System.Int32 1 instance 0 _remoteStackIndex
6ec10740 40000c0 2c System.Object 0 instance 00000000 _dynamicMethods
6ec12d70 40000c1 38 System.Int32 1 instance -2146232062 _HResult
6ec10b24 40000c2 30 System.String 0 instance 00000000 _source
6ec133ec 40000c3 3c System.IntPtr 1 instance 0 _xptrs
6ec12d70 40000c4 40 System.Int32 1 instance -532459699 _xcode
6ec10b24 40030e4 44 System.String 0 instance 00000000 _filename
6ec12d70 40030e5 48 System.Int32 1 instance 0 _line
6ec10b24 4000253 4c System.String 0 instance 010c0cd4 _firstFilename
6ec12d70 4000254 54 System.Int32 1 instance 147 _firstLine
6ebe42f4 4000255 50 System.Object[] 0 instance 00000000 _errors
And when I tried view this inner exceptions message @ 050a633c
0:000> !do 050a633c
Name: System.String
MethodTable: 6ec10b24
EEClass: 6e9cd65c
Size: 206(0xce) bytes
GC Generation: 0
(C:\Windows\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll)
String: Unrecognized configuration section system.serviceModel.
Fields:
MT Field Offset Type VT Attr Value Name
6ec12d70 4000096 4 System.Int32 1 instance 95 m_arrayLength
6ec12d70 4000097 8 System.Int32 1 instance 55 m_stringLength
6ec11804 4000098 c System.Char 1 instance 55 m_firstChar
6ec10b24 4000099 10 System.String 0 shared static Empty
>> Domain:Value 0034b260:050601d0 <<
6ec11754 400009a 14 System.Char[] 0 shared static WhitespaceChars
>> Domain:Value 0034b260:05060a7c <<
Finally, I was able to figure out that the error in this scenario was due to unrecognized configuration section System.ServiceModel.
Step that lead to Solution:
I checked one of my lab machines for the Machine.Config file located at: C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG
On comparing the Machine.Config files from customer’s environment with the file in the lab machine, I found that the following System.ServiceModel section group was missing in customer’s environment.
<sectionGroup name="system.serviceModel" type="System.ServiceModel.Configuration.ServiceModelSectionGroup, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
<section name="behaviors" type="System.ServiceModel.Configuration.BehaviorsSection, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<section name="bindings" type="System.ServiceModel.Configuration.BindingsSection, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<section name="client" type="System.ServiceModel.Configuration.ClientSection, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<section name="comContracts" type="System.ServiceModel.Configuration.ComContractsSection, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<section name="commonBehaviors" type="System.ServiceModel.Configuration.CommonBehaviorsSection, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowDefinition="MachineOnly" allowExeDefinition="MachineOnly"/>
<section name="diagnostics" type="System.ServiceModel.Configuration.DiagnosticSection, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<section name="extensions" type="System.ServiceModel.Configuration.ExtensionsSection, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<section name="machineSettings" type="System.ServiceModel.Configuration.MachineSettingsSection, SMDiagnostics, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowDefinition="MachineOnly" allowExeDefinition="MachineOnly"/>
<section name="serviceHostingEnvironment" type="System.ServiceModel.Configuration.ServiceHostingEnvironmentSection, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<section name="services" type="System.ServiceModel.Configuration.ServicesSection, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
</sectionGroup>
As soon as I copied this section into the Machine.Config file on the faulty customer’s server and tried to restart the SSIS service, EUREKA !!! SSIS Service started successfully .
We were not sure as to what could have modified the Machine.Config file in such a drastic way. Customer only mentioned about applying few regular Microsoft Updates along with other third party product updates.
Author : Ajay(MSFT), SQL Developer Engineer, Microsoft
Reviewed by : SMAT(MSFT), SQL Escalation Services, Microsoft