Differences
This shows you the differences between the selected revisions of the page.
| 2012-05-31 | 2012-06-04 | ||
| draft (martin) | finished (martin) | ||
| Line 9: | Line 9: | ||
| ===== Using from SSIS ===== | ===== 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((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''); |
| * Drag //Script task// from //Toolbox// onto //Control flow// view of the package; | * Drag //Script task// from //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 //Script// page, click //Edit script// button; |
| + | * Visual Studio Tools for Applications is run to edit the script; | ||
| + | * Use //Project > Add Reference > Browse// to add reference to ''winscp.dll''; | ||
| + | * Place your C# or VB.NET code into ''ScriptMain.Main'' method (see example below); | ||
| + | * Close Visual Studio Tools for Applications and //Script task editor// with //OK// button. | ||
| + | |||
| + | ===== Example C# Script Task Code ===== | ||
| + | <code csharp> | ||
| + | using System; | ||
| + | using Microsoft.SqlServer.Dts.Runtime; | ||
| + | using Microsoft.SqlServer.Dts.Tasks.ScriptTask; | ||
| + | using System.AddIn; | ||
| + | using WinSCP; | ||
| + | |||
| + | namespace ST_5a30686e70c04c5a8a93729fd90b8c79.csproj | ||
| + | { | ||
| + | [AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")] | ||
| + | public partial class ScriptMain : VSTARTScriptObjectModelBase | ||
| + | { | ||
| + | public void Main() | ||
| + | { | ||
| + | // Setup session options | ||
| + | SessionOptions sessionOptions = new SessionOptions | ||
| + | { | ||
| + | Protocol = Protocol.Sftp, | ||
| + | HostName = "localhost", | ||
| + | UserName = "test", | ||
| + | Password = "test", | ||
| + | SshHostKey = "ssh-rsa 1024 5e:89:de:a1:1d:ce:d9:c7:f1:3e:23:1b:fd:25:6d:44" | ||
| + | }; | ||
| + | |||
| + | try | ||
| + | { | ||
| + | using (Session session = new Session()) | ||
| + | { | ||
| + | // As WinSCP .NET assembly has to be stored in GAC to be used with SSIS, | ||
| + | // you need to set path to WinSCP.exe explicitly, if using non-default location. | ||
| + | session.ExecutablePath = @"C:\winscp\winscp.exe"; | ||
| + | |||
| + | // Connect | ||
| + | session.Open(sessionOptions); | ||
| + | |||
| + | // Upload files | ||
| + | TransferOptions transferOptions = new TransferOptions(); | ||
| + | transferOptions.TransferMode = TransferMode.Binary; | ||
| + | |||
| + | TransferOperationResult transferResult; | ||
| + | transferResult = session.PutFiles(@"d:\toupload\*", "/home/user/", false, transferOptions); | ||
| + | |||
| + | // Throw on any error | ||
| + | transferResult.Check(); | ||
| + | |||
| + | // Print results | ||
| + | foreach (TransferEventArgs transfer in transferResult.Transfers) | ||
| + | { | ||
| + | Dts.Log(string.Format("Upload of {0} succeeded", transfer.FileName), 0, null); | ||
| + | } | ||
| + | } | ||
| + | |||
| + | Dts.TaskResult = (int)DTSExecResult.Success; | ||
| + | } | ||
| + | catch (Exception e) | ||
| + | { | ||
| + | Dts.Log(string.Format("Error when using WinSCP to upload files: {0}", e), 0, null); | ||
| + | |||
| + | Dts.TaskResult = (int)DTSExecResult.Failure; | ||
| + | } | ||
| + | } | ||
| + | } | ||
| + | } | ||
| + | </code>; | ||