<! This is a short story about a how technical support engineer (Mr. Y)  saved the day for one of his Enterprise customer (Mr. X) >

One fine morning Mr. X logged into his PC to start working on his pending SSIS project. He wanted to make sure his machine has all the necessary settings available to successfully get him started with the project. He was almost done finishing his checklist and when he was about to close the Windows services console , he noticed something that would ruin his entire day. He noticed that the SQL Server Integration Service was not running. He said "what's the big deal ?" and tried to start the service. His hopes came crashing down when he noticed the error message as below :

"Damn!" , He cursed his PC. He started googling around for the error , but nothing helped him to fix his problem. After spending his entire afternoon trying to get this fixed , he gave up.  He called up his friend , who gave him a variety of ideas , but none of them turned out to be productive. Finally he decided to contact Microsoft Support , to fix his problem.

The flow of the support call was as follows :

The Support professional Mr. Y checked the event viewer logs on Mr. X's machine after he tried to reproduce the issue once. There were 2 error messages in the System event logs that caught Mr. Y's attention.  

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:          AJAYSERV2003

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:          AJAYSERV2003

Description:

Timeout (30000 milliseconds) waiting for the SQL Server Integration Services service to connect.

 

Mr. Y knew what exactly had to be done as the next step to dig deeper into this. Mr. Y updated Mr. X that he would be collecting few logs to understand what could be going wrong behind the screens. 

 

 The path of troubleshooting:

Mr. Y  enabled the Sqldumper.exe utility to generate dump files by following this article: http://support.microsoft.com/kb/941151  This article elaborated 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.

Mr. Y tried to start the service once again. This generated the dump file in the above specified log location. To look into the dump file , Mr. Y needed windbg.  He Updated Mr. X that he would need to download Debugging tools for windows.

After installing the tools , Mr. Y launched Windbg and loaded the dump file into the workspace. The very first he 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 Mr.Y 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 , he 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 , Mr. Y 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 he 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, he was able to figure out that the error in this scenario was due to Unrecognized configuration section system.serviceModel.

Step leading to Solution:

Mr. Y checked his lab machine for the machine.config file located @ : C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG

On comparing the machine.config files from Mr. X's environment with his , he found that the following system.serviceModel section group was missing in Mr. X'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 he copied this section into the Machine.Config file on the faulty server and tried to restart the SSIS service, EUREKA !!! SSIS Service started successfully. Mr. X's problem was thus resolved.