October 9, 2017 at 8:45 am
Hi Guys,
I need help, I am not good in C#. Below is C# code that I am using to download the file from SFTP. The code was original to upload the file on SFTP. However, I change it a little bit. I am using this C# code in SSIS "Script Task". Could you please anyone help me where I am doing wrong or show me the right path that would be great. Here is the link where I grab this code. https://winscp.net/eng/docs/library_ssisOne more question, In C# code, am I able to connect SFTP without using SshHostKeyFingerprint ?
Thanks in advance.
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,
// 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.
(string)Dts.Variables["User::HostName"].Value,
(string)Dts.Variables["User::UserName"].Value,
(string)Dts.Variables["User::Password"].Value,
//////SshHostKeyFingerprint = (string)Dts.Variables["User::SshHostKeyFingerprint"].Value
};
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);
transferResult = session.GetFiles(@"C:\\BackUp\\*", "/home", 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;
}
}
}
}
October 9, 2017 at 2:06 pm
You asked for someone to say what you are doing wrong without saying what was or was not taking place when you tried to run this package. Does it give you an error? Keep in mind, that if one of your changes was to take out the SSH Fingerprint part, you may have effectively disabled the security piece and thus any attempt to log in to the SFTP site may fail for that reason, but without knowing what you actually tried and what happened when you did, we have nothing but guesswork to rely on.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 9, 2017 at 10:38 pm
sgmunson sorry, if my question is not clear. I thought it is pretty straightforward. I am using Script Task >>C# script to download the file from SFTP. Above is the code that I am using. However, I am getting error everytime I run Script Task. Here is the error
" at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()"at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()"
October 10, 2017 at 1:00 am
1. First you need to make sure WinSCPnet.dll to Global Assembly Cache (GAC). For SSIS you need to register your WinSCPnet.dll to Global Assembly Cache (GAC).
2. to download file from SFTP check this Session.GetFiles Method Example
Hope it helps.
October 12, 2017 at 1:58 pm
rocky_498 - Monday, October 9, 2017 10:38 PMsgmunson sorry, if my question is not clear. I thought it is pretty straightforward. I am using Script Task >>C# script to download the file from SFTP. Above is the code that I am using. However, I am getting error everytime I run Script Task. Here is the error" at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()"at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()"
Is anyone can help me? Feel free to let me know If my question still not clear.
I'll defer to those with more exposure to C# than I do. twindevil posted what is likely good advice.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply