Setting up WinSCP SSIS package for SQL server agent job

Advertisement

Guest

Setting up WinSCP SSIS package for SQL server agent job

Greetings,

I was given the task to automatize file transfer and load from an SFTP source into our data warehouse (MS SQL). The SSIS package which I created runs like a charm in Visual Studio (on client machine) but fails miserably on the SQL server (deployed on SSISDB and run with SQL server agent job).

Details:
If needed I can provide code snippet.
I would really appreciate if someone could help me with this.

Thank you very much in advance.

Reply with quote

Advertisement

martin
Site Admin
martin avatar

Re: Setting up WinSCP SSIS package for SQL server agent job

Sorry, but that's too vague. What did you try exactly and what did you get?

Reply with quote

Guest

Re: Setting up WinSCP SSIS package for SQL server agent job

I would just provide my code:
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using WinSCP;
using System.Collections.Generic;
#endregion
 
namespace ST_331963c4d11c478fb003abcd00418809
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        static ScriptMain() // code responsible for GAC
        {
            AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
        }
        static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
        {
            if (args.Name.Contains("WinSCPnet"))
            { 
                string path = @"\\server\WinSCP\";
                return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "WinSCPnet.dll"));
            }
 
            return null;
        }
        public void Main()
        {
            try
            {
                SessionOptions sessionOptions = new SessionOptions
                {
                    Protocol = Protocol.Sftp,
                    HostName = "hostnem",
                    UserName = "username",
                    Password = "password",
                    SshHostKeyFingerprint = "fingerprint",
                };
 
                sessionOptions.AddRawSettings("ProxyMethod", "2");
                sessionOptions.AddRawSettings("ProxyHost", "someproxy");
                sessionOptions.AddRawSettings("ProxyPort", "someport");
                sessionOptions.AddRawSettings("ProxyUsername", "someusername");
                sessionOptions.AddRawSettings("ProxyPassword", "Somepw");
          
                using (Session session = new Session())
                {
                    session.Open(sessionOptions);
 
                    TransferOptions transferOptions = new TransferOptions();
                    transferOptions.TransferMode = TransferMode.Binary;
 
                    TransferOperationResult transferResult = session.GetFiles(
                        "/*.csv", @"\\server\folder\", false, transferOptions);
 
                    transferResult.Check();
 
                    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);
 
                        session.MoveFile(transfer.FileName, "/Archive/*");
                    }
                }
 
                Dts.TaskResult = (int)DTSE xecResult.Success;
            }
            catch (Exception e)
            {
                Variables exceptionVariable = null;
                Dts.VariableDispenser.LockOneForWrite("User::problemvar", ref exceptionVariable);
 
                exceptionVariable["User::problemvar"].Value = e.Message;
                exceptionVariable.Unlock();
 
                Dts.Events.FireError(0, null,
                    string.Format("Error when using WinSCP to upload files: {0}", e),
                    null, 0);
 
                Dts.TaskResult = (int)DTS ExecResult.Failure;
            }
        }
    }
}

Reply with quote

Advertisement

Guest

I managed to get the error msg:
An attempt was made to load an assembly from a network location which would have caused the assembly to be sandboxed in previous versions of the .NET Framework. This release of the .NET Framework does not enable CAS policy by default, so this load may be dangerous. If this load is not intended to sandbox the assembly, please enable the loadFromRemoteSources switch. See http://go.microsoft.com/fwlink/?LinkId=155569 for more information.RuntimePropertyInfo:System.Collections.ListDictionaryInternalRuntimePropertyInfo:RuntimePropertyInfo:System.Reflection.RuntimeAssembly nLoadFile(System.String, System.Security.Policy.Evidence)RuntimePropertyInfo: at System.Reflection.RuntimeAssembly.nLoadFile(String path, Evidence evidence)
I am attempting to solve this now...

Reply with quote

Advertisement

You can post new topics in this forum