c# - 如何修复 Biml-Built SSIS 脚本任务的失败任务:不支持此类接口
问题描述
我一直在测试通过 Biml 为 SSIS 包创建脚本任务。我希望能够成功地在本地执行/测试包。
我无法从我的本地开发环境中执行项目的包,因为它们都出错并出现以下相同的错误。
问题:
Error: 0x0 at ScriptTask 1, Script Task : Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{2CD38B23-6C17-4025-A8B6-D2E497DD1DDC}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).
at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
at ScriptMain.Main()
Error: 0x6 at ScriptTask 1: The script returned a failure result.
Task failed: ScriptTask 1
在将项目从 Visual Studio 部署到该服务器(SQL Server 2016)上的 SSIS 目录(SSISDB)后,我能够从另一台服务器成功执行这些包。
我对 AssemblyInfo 和 ScriptMain 使用了以下参考:
- Varigence 文档代码示例:
https ://www.varigence.com/Documentation/Samples/Biml/Script+Task+Project - BimlScript 代码示例:http ://bimlscript.com/Snippet/Details/74
<Script ProjectCoreName="ST_232fecafb70a4e8a904cc21f8870eed0" Name="ScriptTask 1">
<ScriptTaskProject>
<ScriptTaskProject ProjectCoreName="ST_c41ad4bf47544c49ad46f4440163feae" Name="TaskScriptProject1">
<AssemblyReferences>
<AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS.dll" />
<AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask.dll" />
<AssemblyReference AssemblyPath="System.dll" />
<AssemblyReference AssemblyPath="System.AddIn.dll" />
<AssemblyReference AssemblyPath="System.Data.dll" />
<AssemblyReference AssemblyPath="System.Windows.Forms.dll" />
<AssemblyReference AssemblyPath="System.Xml.dll" />
</AssemblyReferences>
<Files>
<File Path="AssemblyInfo.cs">
using System.Reflection;
using System.Runtime.CompilerServices;
//
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
//
[assembly: AssemblyTitle("ST_c41ad4bf47544c49ad46f4440163feae.csproj")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Varigence")]
[assembly: AssemblyProduct("ST_c41ad4bf47544c49ad46f4440163feae.csproj")]
[assembly: AssemblyCopyright("Copyright @ Varigence 2013")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
//
// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:
[assembly: AssemblyVersion("1.0.*")]
</File>
<File Path="ScriptMain.cs">
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
// if SSIS2012, use the following line:
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
// if earlier version, use the next line instead of the above line:
// [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
public void Main()
{
try
{
int totalInsertedRowsToDestination = (int)Dts.Variables["User::TotalInsertedRowsToDestination"].Value;
int rowCountNew = (int)Dts.Variables["User::RowCountNew"].Value;
int totalUpdatedRowsToDestination = (int)Dts.Variables["User::TotalUpdatedRowsToDestination"].Value;
int rowCountChanged = (int)Dts.Variables["User::RowCountChanged"].Value;
int totalUnChangedRowsToDestination = (int)Dts.Variables["User::TotalUnChangedRowsToDestination"].Value;
int rowCountUnchanged = (int)Dts.Variables["User::RowCountUnchanged"].Value;
totalInsertedRowsToDestination += rowCountNew;
totalUpdatedRowsToDestination += rowCountChanged;
totalUnChangedRowsToDestination += rowCountUnchanged;
Dts.Variables["User::TotalInsertedRowsToDestination"].Value = totalInsertedRowsToDestination;
Dts.Variables["User::TotalUpdatedRowsToDestination"].Value = totalUpdatedRowsToDestination;
Dts.Variables["User::TotalUnChangedRowsToDestination"].Value = totalUnChangedRowsToDestination;
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.Events.FireError(0, "Script Task ", ex.Message + "\r" + ex.StackTrace, String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
</File>
</Files>
<ReadOnlyVariables>
<Variable Namespace="User" DataType="Int32" VariableName="RowCountNew" />
<Variable Namespace="User" DataType="Int32" VariableName="RowCountChanged" />
<Variable Namespace="User" DataType="Int32" VariableName="RowCountUnchanged" />
</ReadOnlyVariables>
<ReadWriteVariables>
<Variable Namespace="User" DataType="Int32" VariableName="TotalInsertedRowsToDestination" />
<Variable Namespace="User" DataType="Int32" VariableName="TotalUpdatedRowsToDestination" />
<Variable Namespace="User" DataType="Int32" VariableName="TotalUnChangedRowsToDestination" />
</ReadWriteVariables>
</ScriptTaskProject>
</ScriptTaskProject>
<PrecedenceConstraints>
<Inputs>
<Input OutputPathName="SQL Update <#=dstTableName#>.Output" />
</Inputs>
</PrecedenceConstraints>
</Script>
我希望输出是:SSIS package finished: Success
脚本任务中没有错误。
我的环境:
Windows 10 Enterprise 6.3 x64
Microsoft Visual Studio 2015 Shell (integrated): 14.0.23107.0
Microsoft .NET Framework: 4.7.03056
BimlExpress: 1.0
SQL Server Data Tools: 14.0.61705.170
SQL Server 2016 (SP1-GDR): 13.0.4224.16(x64)
解决方案
我在多个不同的环境/机器上本地重现了该错误并确定了一个修复程序。
解决方案:将 SSIS 项目的TargetServerVersion从更改SQL Server 2014
为SQL Server 2016
. 运行包后的结果消息是SSIS package finished: Success
.
为什么:
这些环境中缺少与 IDTSVariables100 接口相关的 SQL Server 2014 TargetServerVersion 所需的部分。该接口与 SQL Server .NET SDK 2017 2016 有关。 https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dts.runtime.wrapper.idtsvariables100?view=sqlserver-2017
关于 TargetServerVersion 和 ProjectVersion,Andy Leonard 在他的博文中解释道,“只要当前的生产版本是 SSIS 2012+,TargetServerVersion 属性可用于使用最新工具维护 SSIS 项目的当前生产版本。只需更改 TargetServerVersion 属性即可将其更新为可用的最新版本下拉列表中的值”。 https://andyleonard.blog/2018/08/a-tale-of-two-properties-ssis-projectversion-and-targetserverversion/
<ProductVersion>14.0.600.250</ProductVersion>
更多的想法在下面的SSDT部分的附加参考中。
如何:
- 在 Visual Studio 中右键单击 SSIS 项目[MySsisProject (SQL Server 2014)]并选择属性。
- 在新打开的属性页中,展开配置属性组并选择常规。然后选择合适的 TargetServerVersion(我的情况是 SQL Server 2016)
- 阅读警告,提及扩展可能存在的问题,并确定是否要继续。
现在 SSIS 项目在项目名称后的括号中包含 SQL Server 2016 MySsisProject (SQL Server 2016)
,. 这解决了这个问题。接下来在本地执行打包,验证打包成功。
测试环境/机器:
环境 :
Windows 10 Enterprise 6.3 x64
Microsoft Visual Studio 2015 Shell (integrated): 14.0.23107.0
Microsoft .NET Framework: 4.7.03056
BimlExpress: 1.0
SQL Server Data Tools: 14.0.61705.170
SQL Server 2016 (SP1-GDR): 13.0.4224.16(x64)
环境 :
Windows 10 Enterprise 6.3 x64
Microsoft Visual Studio Enterprise 2017: 15.9.8
Microsoft .NET Framework: 4.7.03056
BimlExpress: 1.0
SQL Server Data Tools: 15.1.61902.21100
SQL Server 2016 (SP1-GDR): 13.0.4224.16(x64)
环境 :
Windows Server 2012 R2 Datacenter 6.3 x64
Microsoft Visual Studio Professional 2015: 14.0.25431.01 Update 3
Microsoft .NET Framework: 4.7.02053
BimlExpress: 1.0
SQL Server Data Tools: 14.0.61705.170
SQL Server 2016 (SP1): 13.0.4001.0(x64)
SSDT (SQL Server Data Tools) 的附加参考:
- SSDT 旨在向后兼容,有关安装 SSDT 的信息:https ://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017
- 脚本任务失败的示例,然后是 SSDT 版本(与 VS 2017 相关联的 15.9.0)修复问题: https ://feedback.azure.com/forums/908035-sql-server/suggestions/32896399-script-tasks-losing -code-in-ssdt-17-1-for-visual-s https://docs.microsoft.com/en-us/sql/ssdt/release-notes-ssdt?view=sql-server-2017#ssdt- for-visual-studio-2017-1570
- 我发现了一个与“SSIS 脚本任务 - 不支持接口”相关的类似问题,与版本差异有关,并修复了加载旧版本;但是,答案是有限的,并且没有描述上述问题的已识别问题所需的解决方案: SSIS Script Task - No Interface Supported
推荐阅读
- javascript - 如何将数组值作为对象键传递,并将该键与对象中的某些值相关联。然后将其传递给列表项
- c# - 使用 Magick.NET 压缩图像不会减少太多
- php - 用guzzle下载pdf
- java - Spring starter 项目在创建项目时带来错误
- asp.net - 我应该如何处理 MediatR NotificationHandlers 的简单注入器生活方式?
- mysql - mysql数据库恢复到另一个数据库
- html - 为行值添加条件(角度材料)
- javascript - 如何用 then/catch 来“最终”承诺?
- go - Go:动态结构组合
- javascript - 如何将 geojson 文件的内容分配给 Javascript 中的变量?