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 56: 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 99: 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>

Last modified: by martin