Differences
This shows you the differences between the selected revisions of the page.
| library_ssis 2012-06-05 | 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) ====== | ||
| - | &beta_feature | ||
| - | ===== 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((This guide is for Microsoft Visual Studio 2008. The steps may differ for other versions. Do not hesitate to update this document for other versions.)), 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''); |
| - | * Drag //Script task// from //Toolbox// onto //Control flow// view of the package; | + | * Drag //Script Task// from //SSIS Toolbox// onto //Control flow// view of the package; |
| * In the context menu of the task, choose //Edit//; | * In the context menu of the task, choose //Edit//; | ||
| - | * On the //Script task editor// on //Script// page, click //Edit script// button; | + | * On the //Script Task Editor// on the initial //Script// page, click //Edit Script// button; |
| - | * Visual Studio Tools for Applications is run to edit the script; | + | * Another instance of Visual Studio opens to edit the script project; |
| - | * Use //Project > Add Reference > Browse// to add reference to ''winscp.dll''; | + | * Use //Project > Add Reference > Browse// to add reference to ''winscpnet.dll''; |
| - | * Place your C# or VB.NET code into ''ScriptMain.Main'' method (see example below); | + | * Place your C# or VB.NET code into ''ScriptMain.Main'' method (see the example below); |
| - | * Close Visual Studio Tools for Applications and //Script task editor// with //OK// button. | + | * 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. | ||
| + | |||
| + | ===== Deploying WinSCP .NET Assembly ===== | ||
| + | |||
| + | 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. | ||
| ===== [[example]] Example C# Script Task Code ===== | ===== [[example]] Example C# Script Task Code ===== | ||
| Line 23: | 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; | ||
| - | namespace ST_5a30686e70c04c5a8a93729fd90b8c79.csproj | + | namespace ST_5a30686e70c04c5a8a93729fd90b8c79 |
| { | { | ||
| - | [AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")] | + | [SSISScriptTaskEntryPoint] |
| public partial class ScriptMain : VSTARTScriptObjectModelBase | public partial class ScriptMain : VSTARTScriptObjectModelBase | ||
| { | { | ||
| Line 38: | Line 41: | ||
| Protocol = Protocol.Sftp, | Protocol = Protocol.Sftp, | ||
| // To setup these variables, go to SSIS > Variables. | // To setup these variables, go to SSIS > Variables. | ||
| - | // To make them accessible from the script task, in the context menu of the task, | + | // To make them accessible from the script task, in the context menu of the |
| - | // choose Edit. On the Script task editor on Script page, select ReadOnlyVariables, | + | // task, choose Edit. On the Script task editor on Script page, |
| - | // and tick the below properties. | + | // select ReadOnlyVariables, and tick the below properties. |
| HostName = (string) Dts.Variables["User::HostName"].Value, | HostName = (string) Dts.Variables["User::HostName"].Value, | ||
| UserName = (string) Dts.Variables["User::UserName"].Value, | UserName = (string) Dts.Variables["User::UserName"].Value, | ||
| Password = (string) Dts.Variables["User::Password"].Value, | Password = (string) Dts.Variables["User::Password"].Value, | ||
| - | SshHostKey = (string) Dts.Variables["User::SshHostKey"].Value | + | SshHostKeyFingerprint = (string) Dts.Variables["User::Fingerprint"].Value |
| }; | }; | ||
| Line 51: | 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, if using non-default location. | + | // you need to set path to WinSCP.exe explicitly. |
| + | // 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 62: | Line 67: | ||
| transferOptions.TransferMode = TransferMode.Binary; | transferOptions.TransferMode = TransferMode.Binary; | ||
| - | TransferOperationResult transferResult; | + | TransferOperationResult transferResult = session.PutFiles( |
| - | ····················transferResult = session.PutFiles(@"d:\toupload\*", "/home/user/", false, transferOptions); | + | ························@"d:\toupload\*", "/home/user/", false, transferOptions); |
| // Throw on any error | // Throw on any error | ||
| Line 69: | Line 74: | ||
| // Print results | // Print results | ||
| + | bool fireAgain = false; | ||
| foreach (TransferEventArgs transfer in transferResult.Transfers) | foreach (TransferEventArgs transfer in transferResult.Transfers) | ||
| { | { | ||
| Line 92: | 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> | ||