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).
- I am using Script task with .NET code
- The WinSCP Assembly is placed in a folder on the Server (eg.:
- Instead of registering to GAC I am using the workaround proposed here:
- 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:
- 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.