从 SQL Server 代理运行时，引用 WinSCPnet.dll 的 SSIS C# 2012 脚本任务失败，并且“调用的目标已抛出异常" [英] SSIS C# 2012 Script Task referring WinSCPnet.dll fails when run from SQL Server Agent with "Exception has been thrown by the target of an invocation"

问题描述

我有一个包含 C# 2012 脚本任务的 SSIS 包(在 VS 2013 中创建).

I have an SSIS package (created in VS 2013) that contains a C# 2012 Script Task.

脚本任务的工作是使用 WinSCP .NET 程序集从 SFTP 服务器下载文件并将其放置在我的服务器上(Windows Server 2012 R2 和 SQL Server 2014)

The job of the script task is to download a file from an SFTP server using WinSCP .NET assembly and place it on my server (Windows Server 2012 R2 with SQL Server 2014)

当我在我的开发机器上运行它时我的包运行良好，但是当我部署到服务器时我的包在此任务中失败并显示错误消息

My package runs fine when I run it on my Dev machine, but when I deploy to the server my package fails at this task with the error message

调用的目标已抛出异常

我已经做了一些挖掘，看起来它与对 WinSCPnet.dll 的引用有关.

I've done some digging and it looks like it has something to do with the reference to WinSCPnet.dll.

推荐答案

在 引用 WinSCP 文章由调用目标抛出:

这只是一个高级别的例外.根本原因通常存储在 InnerException.

This is just a high-level exception. The root cause is usually stored in the InnerException.

如果您在 SSIS 中遇到此异常，您可以使用 try ... catch 块来捕获错误，如 使用来自 SSIS 的 WinSCP .NET 程序集.

If you are getting this exception in SSIS, you can use try … catch block to capture the error, as show in the example for using WinSCP .NET Assembly from SSIS.

如果您无法轻松访问内部异常，请检查 WinSCP 会话日志和调试日志文件 (Session.SessionLogPath, Session.DebugLogPath).如果甚至没有创建这些文件，根本原因可能是加载了 WinSCPnet.dll 程序集.请参阅 无法加载文件或程序集file:///...WinSCPnet.dll"或其依赖项之一.系统找不到指定的文件..

If you cannot access the inner exception easily, inspect WinSCP session log and debug log file (Session.SessionLogPath, Session.DebugLogPath). If those file are not even created, the root cause can be loading of WinSCPnet.dll assembly. See Could not load file or assembly ‘file:///…WinSCPnet.dll’ or one of its dependencies. The system cannot find the file specified..

---

安装程序集以允许其加载在使用 WinSCP .NET 程序集的安装部分来自SQL Server 集成服务 (SSIS):

首先，您需要安装 WinSCP .NET 程序集.不要使用 NuGet 包.¹

Installing

First, you need to install the WinSCP .NET assembly. Do not use the NuGet package.¹

您还需要将程序集安装到 GAC 或 订阅 AppDomain.AssemblyResolve 事件 以允许加载程序集.

You also need to install the assembly to the GAC or subscribe AppDomain.AssemblyResolve event to allow loading the assembly.

---

安装到 GAC 包含在 安装到 GAC 部分的 WinSCP .NET 程序集安装说明:

在特殊情况下，您可能需要将程序集安装到全局程序集缓存 (GAC) 中，特别是要从 SSIS.

Installing to GAC

In special cases, you may need to install the assembly into Global Assembly Cache (GAC), particularly to use it from SSIS.

当您将程序集安装到 GAC 时，您需要配置 WinSCP 可执行文件的路径.

When you install the assembly to GAC, you need to configure a path to WinSCP executable.

在开发机器上

要将程序集安装到开发机器上的 GAC 中，即具有 Windows SDK 已安装，使用以下命令:

To install the assembly into GAC on development machine, i.e. the one that has Windows SDK installed, use following command:

gacutil.exe /i WinSCPnet.dll

Windows SDK 附带 Microsoft Visual Studio.也可以单独安装.

Windows SDK comes with Microsoft Visual Studio. You can also install it separately.

为您的 .NET 框架版本使用正确的 gacutil.exe:

Use correct gacutil.exe for your version of .NET framework:

• 对于 .NET framework 4.0 或更高版本，请使用 Windows SDK 7.1(或更高版本)中的 gacutil:
  C:Program Files (x86)Microsoft SDKsWindowsv7.1ingacutil.exe;
• 对于 .NET framework 3.5，使用 Windows SDK 6.0 中的 gacutil:
  C:Program Files (x86)Microsoft SDKsWindowsv6.0ABingacutil.exe

在生产或用户机器上

要将程序集安装到生产或用户机器上的 GAC 中，您可以使用以下命令将程序集安装到 GAC 中:

To install the assembly into GAC on production or user’s machine, you may install the assembly into GAC using:

• Windows Installer，通过创建.msi 包；

支持安装到 GAC 的任何其他安装程序系统，例如Inno Setup;

Any other installer system that supports installing to GAC, e.g. Inno Setup;

系统.EnterpriseServices.Internal.Publish.GacInstall 方法.PowerShell 示例:

System.EnterpriseServices.Internal.Publish.GacInstall method. PowerShell example:

Add-Type -AssemblyName "System.EnterpriseServices"
$publish = New-Object System.EnterpriseServices.Internal.Publish
$publish.GacInstall("WinSCPnet.dll")

需要指定 DLL 的绝对路径并且需要管理员权限.否则上述方法将失败(并且将失败的唯一指示发送到 Windows 事件日志).

An absolute path to the DLL needs to be specified and Administrator privileges are required. Otherwise the above method will fail (and the only indication of the failure is sent to Windows Event log).

1. 请参阅如何在 SSIS 脚本任务中修复 NuGet WinSCP.NET?

这篇关于从 SQL Server 代理运行时，引用 WinSCPnet.dll 的 SSIS C# 2012 脚本任务失败，并且“调用的目标已抛出异常"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！