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: http://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