Differences
This shows you the differences between the selected revisions of the page.
| library_ssis 2012-05-31 | 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 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// click //Edit script// button; | + | * On the //Script Task Editor// on the initial //Script// page, click //Edit Script// button; |
| + | * Another instance of Visual Studio opens to edit the script project; | ||
| + | * 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); | ||
| + | * 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 ===== | ||
| + | <code csharp> | ||
| + | using System; | ||
| + | using Microsoft.SqlServer.Dts.Runtime; | ||
| + | using Microsoft.SqlServer.Dts.Tasks.ScriptTask; | ||
| + | using WinSCP; | ||
| + | |||
| + | namespace ST_5a30686e70c04c5a8a93729fd90b8c79 | ||
| + | { | ||
| + | [SSISScriptTaskEntryPoint] | ||
| + | public partial class ScriptMain : VSTARTScriptObjectModelBase | ||
| + | { | ||
| + | public void Main() | ||
| + | { | ||
| + | // Setup session options | ||
| + | SessionOptions sessionOptions = new SessionOptions | ||
| + | { | ||
| + | Protocol = Protocol.Sftp, | ||
| + | // To setup these variables, go to SSIS > Variables. | ||
| + | // To make them accessible from the script task, in the context menu of the | ||
| + | // task, choose Edit. On the Script task editor on Script page, | ||
| + | // select ReadOnlyVariables, and tick the below properties. | ||
| + | HostName = (string) Dts.Variables["User::HostName"].Value, | ||
| + | UserName = (string) Dts.Variables["User::UserName"].Value, | ||
| + | Password = (string) Dts.Variables["User::Password"].Value, | ||
| + | SshHostKeyFingerprint = (string) Dts.Variables["User::Fingerprint"].Value | ||
| + | }; | ||
| + | |||
| + | try | ||
| + | { | ||
| + | using (Session session = new Session()) | ||
| + | { | ||
| + | // If WinSCP .NET assembly has been stored in GAC to be used with SSIS, | ||
| + | // 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"; | ||
| + | |||
| + | // Connect | ||
| + | session.Open(sessionOptions); | ||
| + | |||
| + | // Upload files | ||
| + | TransferOptions transferOptions = new TransferOptions(); | ||
| + | transferOptions.TransferMode = TransferMode.Binary; | ||
| + | |||
| + | TransferOperationResult transferResult = session.PutFiles( | ||
| + | @"d:\toupload\*", "/home/user/", false, transferOptions); | ||
| + | |||
| + | // Throw on any error | ||
| + | transferResult.Check(); | ||
| + | |||
| + | // Print results | ||
| + | bool fireAgain = false; | ||
| + | foreach (TransferEventArgs transfer in transferResult.Transfers) | ||
| + | { | ||
| + | Dts.Events.FireInformation(0, null, | ||
| + | string.Format("Upload of {0} succeeded", transfer.FileName), | ||
| + | null, 0, ref fireAgain); | ||
| + | } | ||
| + | } | ||
| + | |||
| + | Dts.TaskResult = (int)DTSExecResult.Success; | ||
| + | } | ||
| + | catch (Exception e) | ||
| + | { | ||
| + | Dts.Events.FireError(0, null, | ||
| + | string.Format("Error when using WinSCP to upload files: {0}", e), | ||
| + | null, 0); | ||
| + | |||
| + | Dts.TaskResult = (int)DTSExecResult.Failure; | ||
| + | } | ||
| + | } | ||
| + | } | ||
| + | } | ||
| + | </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> | ||