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 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://docs.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)
If needed I can provide code snippet.
I would really appreciate if someone could help me woth 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://docs.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)
If needed I can provide code snippet.
I would really appreciate if someone could help me woth this.
Thank you very much in advance.