SSIS包部署到SQL Server报未识别system.web配置节错误

问题场景 #

将仅包含存储过程执行、Excel导入流程的SSIS包部署到已创建SSIS目录的SQL Server实例时触发报错，检索公开的异常解决方案未找到有效处理方法。
当前环境为SQL Server 2012（版本号11.00.7001），配套对应版本SSIS服务。

核心报错提示 #

Unrecognized configuration section system.web/system.web.
(C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config line 243)

完整错误栈 #

A .NET Framework error occurred during execution of user-defined routine or aggregate "deploy_project_internal": 
System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception. 
---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnectionFactory' threw an exception. 
---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlPerformanceCounters' threw an exception. 
---> System.Configuration.ConfigurationErrorsException: Configuration system failed to initialize 
---> System.Configuration.ConfigurationErrorsException: Unrecognized configuration section system.web/system.web.
(C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config line 243)
System.Configuration.ConfigurationErrorsException: 
   at System.Configuration.ConfigurationSchemaErrors.ThrowIfErrors(Boolean ignoreLocal)
   at System.Configuration.BaseConfigurationRecord.ThrowIfParseErrors(ConfigurationSchemaErrors schemaErrors)
   at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey)
System.Configuration.ConfigurationErrorsException: 
   at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey)
   at System.Configuration.ClientConfigurationSystem.PrepareClientConfigSystem(String sectionName)
   at System.Configuration.ClientConfigurationSystem.System.Configuration.Internal.IInternalConfigSystem.GetSection(String sectionName)
   at System.Configuration.Co
    ...
System.TypeInitializationException: 
   at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
   at Microsoft.SqlServer.IntegrationServices.Server.ServerConnectionControl.GetOperationInfo(SqlInt64 operation_id, OperationType type, ServerOperationStatus status, Int64& object_id)
   at Microsoft.SqlServer.IntegrationServices.Server.ServerConnectionControl.checkParameters(SqlInt64 object_id, SqlInt64 operation_id, OperationType type)
   at Microsoft.SqlServer.IntegrationServices.Serve... (.Net SqlClient Data Provider)

------------------------------
Server Name: database
Error Number: 6522
Severity: 16
State: 1
Procedure: deploy_project_internal
------------------------------
Program Location:

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.SqlServer.IntegrationServices.Common.ObjectModel.SqlHelper.ExecuteSQLCommand(SqlStoreConnection storeConnection, CommandType cmdType, String cmdText, SqlParameter[] parameters, ExecuteType execType, Int32 commandTimeout)
   at Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder.DeployProject(String projectName, Byte[] projectStream)
   at Microsoft.SqlServer.IntegrationServices.Wizard.Common.Model.DeployExportProjectHelper.DeployProject(CatalogFolder folder, Project project)
   at Microsoft.SqlServer.IntegrationServices.Wizard.Common.Model.DeploymentModel.Deploy(CatalogFolder folder, Project project)
   at Microsoft.SqlServer.IntegrationServices.Wizard.Common.Model.DeploymentModel.DeployProject()
   at Microsoft.SqlServer.IntegrationServices.Deployment.DeployProjectPage.DoWork(Object sender, DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

相关参考截图 #

• 报错截图
• SSIS/SQL Server版本截图1
• SSIS/SQL Server版本截图2

补充配置信息 #

排查发现服务器64位.NET Framework 4.0的machine.config中system.web节点预期合法结构如下：

<system.web>
        <processModel autoConfig="true"/>
        <httpHandlers/>
        <membership>
            <providers>
                <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression=""/>
            </providers>
        </membership>
        <profile>
            <providers>
                <add name="AspNetSqlProfileProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Profile.SqlProfileProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
            </providers>
        </profile>
        <roleManager>
            <providers>
                <add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
                <add name="AspNetWindowsTokenRoleProvider" applicationName="/" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
            </providers>
        </roleManager>
    </system.web>

解决方案 #

该错误本质是.NET全局配置文件machine.config存在格式错误，system.web节点下被错误嵌套了第二层同名system.web节点，导致SSIS部署时调用的.NET CLR无法正常解析配置，触发类型初始化失败。
按以下步骤修复即可：

• 先备份路径C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\下的machine.config文件，防止修改失误导致环境故障。
• 以管理员权限启动文本编辑器，打开上述machine.config文件，直接定位到第243行。
• 检查该行位置，删除嵌套在第一层<system.web>节点内部的多余<system.web>起始标签，以及和该多余标签配对的</system.web>闭合标签，保证system.web节点仅存在一层，结构和上述给出的合法结构一致。
• 保存修改后的配置文件，重启SQL Server数据库引擎服务与SSIS服务，重新执行SSIS包部署操作即可恢复正常。

内容的提问来源于stack exchange，提问作者Jeff