Search code examples
ssissftpwinscp

Need to pass SSIS variable to the Execute Process task to make Dynamic command line argument for Winscp

I have a folder 'DATA' at SFTP location from where I need to download the set of files to some common location and then copy the respective files to different folder location.

File Names are:

Test1.csv
Test2.csv
Test3.csv
Test4.csv
Test5.csv

I want that files first gets downloaded to below location:

G:\USER_DATA\USER_USER_SYNC\Download

Since these files are related to different schema and have to processed separately by each different ssis packages for further transformations and loading. For some reasons we have to first keep it at some common location and then move or copy afterwards. Here's my command line argument.

/log=G:\USER_DATA\USER_USER_SYNC\SFTP_LOG\user_sync_winscp.log /command "open sftp://username:[email protected]/" -hostkey=""ssh-rsa 2048 9b:63:5e:c4:26:bb:35:0d:49:e6:74:5e:5a:48:c0:8a""" "get /DATA/Test1.csv G:\USER_DATA\USER_USER_SYNC\Download\" "exit"

Using above, I am able to download a given file one file at a time.

Since, I need to have first it at some common folder location. Hence I am planning to add another Execute process task to copy the files.

/C copy /b G:\USER_DATA\USER_USER_SYNC\Download\Test1.csv G:\USER_DATA\USER_USER_SYNC\Testing1

/C copy /b G:\USER_DATA\USER_USER_SYNC\Download\Test1.csv G:\USER_DATA\USER_USER_SYNC\Testing2

and so on...

I am looking for some way, using which we can download all the available files to some common folder location and then move or copy to different folder locations.

Solution

  • I have changed the design and followed a new approach. Thanks to Martin for fixing the sftp related issues and continuous support.

    ssis package New SSIS package has below tasks:

    Step1. It will look for latest updated files on sftp server and download the given files Test1.csv and Test2.csv to location G:\USER_DATA\USER_USER_SYNC\Download\

    Here's my command line arguments:

    /log=G:\USER_DATA\USER_USER_SYNC\SFTP_LOG\user_sync_winscp.log /command "open sftp://bisftp:*UFVy2u6jnJ]#hU0Zer5AjvDU4#[email protected]/ -hostkey=""ssh-rsa 2048 9b:63:5e:c4:26:bb:35:0d:49:e6:74:5e:5a:48:c0:8a""" "cd /DATA" "get -filemask=">=today" Test1.csv Test2.csv G:\USER_DATA\USER_USER_SYNC\Download\" "exit"

    Step-2. Since my requirement was to further copy each file to different folder location, so that respective process can pick corresponding file and start transformation and loading it into sql server.

    This step will execute the Window cmd process and copy Test1.csv to new location as

    G:\USER_DATA\USER_USER_SYNC\Testing1

    command line arguments as:

    /C copy /b G:\USER_DATA\USER_USER_SYNC\Download\Test1.csv G:\USER_DATA\USER_USER_SYNC\Testing1

    Like wise I have another Execute process task to copy Test2.csv to new location as

    G:\USER_DATA\USER_USER_SYNC\Testing2

    command line arguments as:

    /C copy /b G:\USER_DATA\USER_USER_SYNC\Download\Test2.csv G:\USER_DATA\USER_USER_SYNC\Testing2

    The given solution is working fine, However there are couple of things which still needs to be handle.

    Since I am downloading the latest file only using -filemask=">=today". Everything runs fine if execute process task is able to find the latest files on sftp server. If it's not there, than the next subsequent execute process task is failing with below error message. The returning The process exit code was "1" while the expected was "0"

    Here what I understand is that it's failing as it has nothing to copy or move. Is there any way by which we can capture the exit code returned from first execute process task and store it into some variable, so that we can use expression to decide that whether to start next task or not.

    Second, as you can see that I am using two execute process task to copy files from one location to another. Can we do anything to combine both these two commands into one execute process task?

    Any suggestion most welcome and also i think that this issue needs to be addressed as a separate question.