Freigeben über

Troubleshooting the SSIS Service Start-up Failure issue

  • Artikel

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.

 

clip_image002

 

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

Comments

  • Anonymous
    May 12, 2015
    Very helpful,slove problem.