Differences
This shows you the differences between the selected revisions of the page.
| library_ssis 2019-05-21 | library_ssis 2022-12-08 (current) | ||
| Line 1: | Line 1: | ||
| ====== Using WinSCP .NET Assembly from SQL Server Integration Services (SSIS) ====== | ====== Using WinSCP .NET Assembly from SQL Server Integration Services (SSIS) ====== | ||
| - | ===== Installing ===== | + | ===== [[installing]] Installing ===== |
| - | First, you need to [[library_install|install the WinSCP .NET assembly]]. | + | First, you need to [[library_install|download the WinSCP .NET assembly]]. Do not use the NuGet package.((See [[https://stackoverflow.com/q/55457058/850848|How to fix NuGet WinSCP.NET in SSIS Script Task?]])) |
| - | You also need to [[library_install#gac|install the assembly to the GAC]].((SSIS can only use assemblies installed to GAC.)) | + | You also need to [[library_install#gac|install the assembly to the GAC]] or [[#subscribe|subscribe ''AppDomain.AssemblyResolve'' event]] in your code to allow loading the assembly. |
| - | ===== Using from SSIS ===== | + | ===== [[using]] Using from SSIS ===== |
| You use WinSCP .NET assembly from SSIS as any other .NET assembly: | You use WinSCP .NET assembly from SSIS as any other .NET assembly: | ||
| * In Microsoft Visual Studio, in your "Integration Services Project", choose your "SSIS Package" (e.g. the default ''Package.dtsx''); | * In Microsoft Visual Studio, in your "Integration Services Project", choose your "SSIS Package" (e.g. the default ''Package.dtsx''); | ||
| Line 15: | Line 15: | ||
| * Use //Project > Add Reference > Browse// to add reference to ''winscpnet.dll''; | * Use //Project > Add Reference > Browse// to add reference to ''winscpnet.dll''; | ||
| * Place your C# or VB.NET code into ''ScriptMain.Main'' method (see the example below); | * Place your C# or VB.NET code into ''ScriptMain.Main'' method (see the example below); | ||
| + | * If you have chosen the GAC approach, you need to [[library_install#installing|set ''Session.ExecutablePath'']]. Alternatively, [[#subscribe|subscribe ''AppDomain.AssemblyResolve'' event]]. | ||
| * Close Visual Studio of the script project. Close //Script Task Editor// with //OK// button. | * Close Visual Studio of the script project. Close //Script Task Editor// with //OK// button. | ||
| ===== Deploying WinSCP .NET Assembly ===== | ===== Deploying WinSCP .NET Assembly ===== | ||
| - | When deploying your SSIS package, WinSCP .NET assembly should be [[library_install#gac|installed to GAC]] to be accessible. | + | If you used GAC when developing your SSIS package, WinSCP .NET assembly needs to be [[library_install#gac|installed to GAC]] even on the target machine. |
| - | + | ||
| - | Alternatively, you can subscribe ''[[dotnet>system.appdomain.assemblyresolve|AppDomain.AssemblyResolve]]'' event in a static constructor handler of the script task class to locate the assembly in an another location. For details, see article [[https://blogs.msdn.microsoft.com/dbrowne/2014/06/25/how-to-load-an-assembly-in-a-ssis-script-task-that-isnt-in-the-gac/|How to load an Assembly in a SSIS script task that isn't in the GAC]]. | + | |
| ===== [[example]] Example C# Script Task Code ===== | ===== [[example]] Example C# Script Task Code ===== | ||
| Line 29: | Line 27: | ||
| using Microsoft.SqlServer.Dts.Runtime; | using Microsoft.SqlServer.Dts.Runtime; | ||
| using Microsoft.SqlServer.Dts.Tasks.ScriptTask; | using Microsoft.SqlServer.Dts.Tasks.ScriptTask; | ||
| - | using System.AddIn; | ||
| using WinSCP; | using WinSCP; | ||
| Line 57: | Line 54: | ||
| using (Session session = new Session()) | using (Session session = new Session()) | ||
| { | { | ||
| - | // As WinSCP .NET assembly has to be stored in GAC to be used with SSIS, | + | // If WinSCP .NET assembly has been stored in GAC to be used with SSIS, |
| - | // you need to set path to WinSCP.exe explicitly, | + | // you need to set path to WinSCP.exe explicitly. |
| - | // if using non-default location. | + | // This is not needed if you have subscribed AppDomain.AssemblyResolve event |
| + | // and the WinSCP.exe is in the same location as WinSCPnet.dll. | ||
| session.ExecutablePath = @"C:\winscp\winscp.exe"; | session.ExecutablePath = @"C:\winscp\winscp.exe"; | ||
| Line 100: | Line 98: | ||
| </code> | </code> | ||
| + | ===== [[subscribe]] Subscribing AppDomain.AssemblyResolve ===== | ||
| + | |||
| + | If you do not want to [[library_install#gac|install the assembly to the GAC]], you can instead subscribe ''[[dotnet>system.appdomain.assemblyresolve|AppDomain.AssemblyResolve]]'' event in a static constructor of the script task class, to locate the assembly in another location. For details, see article [[https://learn.microsoft.com/en-us/archive/blogs/dbrowne/how-to-load-an-assembly-in-a-ssis-script-task-that-isnt-in-the-gac|How to load an Assembly in a SSIS script task that isn't in the GAC]]. | ||
| + | |||
| + | <code csharp> | ||
| + | using System; | ||
| + | using System.IO; | ||
| + | using System.Reflection; | ||
| + | using Microsoft.SqlServer.Dts.Runtime; | ||
| + | using Microsoft.SqlServer.Dts.Tasks.ScriptTask; | ||
| + | using WinSCP; | ||
| + | |||
| + | namespace ST_5a30686e70c04c5a8a93729fd90b8c79 | ||
| + | { | ||
| + | [SSISScriptTaskEntryPoint] | ||
| + | public partial class ScriptMain : VSTARTScriptObjectModelBase | ||
| + | { | ||
| + | private const string LoadingLog = @"C:\winscp\loading.log"; | ||
| + | private const string AssemblyPath = @"C:\winscp\WinSCPnet.dll"; | ||
| + | |||
| + | static ScriptMain() | ||
| + | { | ||
| + | DebugLoading("Setting up assembly resolve handler"); | ||
| + | AppDomain.CurrentDomain.AssemblyResolve += AssemblyResolve; | ||
| + | } | ||
| + | |||
| + | private static void DebugLoading(string message) | ||
| + | { | ||
| + | message = DateTime.Now.ToLongTimeString() + ": " + message + Environment.NewLine; | ||
| + | // Uncomment to debug assembly loading issues | ||
| + | // File.AppendAllText(LoadingLog, message); | ||
| + | } | ||
| + | |||
| + | private static Assembly AssemblyResolve(object sender, ResolveEventArgs args) | ||
| + | { | ||
| + | try | ||
| + | { | ||
| + | DebugLoading($"Resolving assembly {args.Name}"); | ||
| + | string name = new AssemblyName(args.Name).Name; | ||
| + | DebugLoading($"Assembly name {name}"); | ||
| + | if (name.Equals("WinSCPnet", StringComparison.InvariantCultureIgnoreCase)) | ||
| + | { | ||
| + | DebugLoading($"Loading {name} from {AssemblyPath}"); | ||
| + | Assembly assembly = Assembly.LoadFile(AssemblyPath); | ||
| + | DebugLoading("Loaded"); | ||
| + | return assembly; | ||
| + | } | ||
| + | DebugLoading("Not WinSCPnet"); | ||
| + | return null; | ||
| + | } | ||
| + | catch (Exception e) | ||
| + | { | ||
| + | DebugLoading($"Exception: {e}"); | ||
| + | throw; | ||
| + | } | ||
| + | } | ||
| + | } | ||
| + | } | ||
| + | </code> | ||