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:
I would really appreciate if someone could help me with this.
Thank you very much in advance.
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:
- I am using Script task with .NET code
- The WinSCP Assembly is placed in a folder on the Server (eg.:
//server/xy/winscpnet.dll
)
- Instead of registering to GAC I am using the workaround proposed here:
https://learn.microsoft.com/en-us/archive/blogs/dbrowne/how-to-load-an-assembly-in-a-ssis-script-task-that-isnt-in-the-gac
- SFTP server and user (and proxy) credentials are defined within the code
- As mentioned runs very smooth from the client machine of mine.
- When running from Job agent the Script task fails with the following error msg:
https://winscp.net/eng/docs/message_net_exception_thrown_target_invocation
- Tried to log the run but the log is empty
- The SSISDB proxy has access to the folder (as well as the server technical user as we are getting other files on a daily basis)
I would really appreciate if someone could help me with this.
Thank you very much in advance.