3

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:password@stransfer.host.com/" -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.

vikrant rana
  • 4,509
  • 6
  • 32
  • 72
  • 1
    What about this? [Passing Variable values into .bat file using Execute Process Task](https://stackoverflow.com/q/51257928/850848) - and many others. – Martin Prikryl Aug 27 '19 at 13:48
  • 1
    Though a way more efficient would be to put all files into one command-line. Otherwise you will end up opening a new connection for each file. – Martin Prikryl Aug 27 '19 at 13:49
  • Thanks Martin Prikryl. I will have a look at this option as well. Thanks. – vikrant rana Aug 27 '19 at 14:21
  • How we can process with later option. to put all the files into one command-line. This option looks more efficient. – vikrant rana Aug 27 '19 at 14:48
  • 2
    Seems like you're really close. Just set an expression on the "Arguments" property on the Execute Process Task. – Tim Mylott Aug 27 '19 at 15:46
  • 1
    *"How we can process with later option. to put all the files into one command-line"* - You need to do something like `"cd /DATA" "get Test1.csv Test2.csv Test3.csv ... G:\USER_DATA\USER_USER_SYNC\"`. You need to generate the `Test1.csv Test2.csv Test3.csv` out of your `FileNameList`. That's probably easy, but I not know how to do that in SSIS. You may want to ask a more specific question about that. – Martin Prikryl Aug 28 '19 at 05:12
  • @ Martin Prikryl. this thing works well "cd /DATA" "get Test1.csv Test2.csv Test3.csv.. this way we need not to make every time a new connection. you can post this as an answer. I will accept that. Just asking can we also assign different location for destination as well. for example.. Test1.csv should go to G:\USER_DATA\USER_USER_SYNC\Download1\ and Test2.csv should go to G:\USER_DATA\USER_USER_SYNC\Download2\. Thanks – vikrant rana Aug 28 '19 at 09:56
  • So how did you generate the `Test1.csv Test2.csv Test3.csv` from your SQL table? – Martin Prikryl Aug 28 '19 at 10:31
  • 1
    If you need different locations, just repeat the `get` command, like: `"get Test1.csv G:\USER_DATA\USER_USER_SYNC\Download1\" "get Test2.csv G:\USER_DATA\USER_USER_SYNC\Download2\"` – Martin Prikryl Aug 28 '19 at 10:38
  • Many Thanks for your kind help. These commands are really helpful. – vikrant rana Aug 28 '19 at 10:59
  • 1
    If you have a working solution, please post it as an answer. – Martin Prikryl Aug 29 '19 at 11:13

1 Answers1

1

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#K3m@stransfer.host.com/ -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.

vikrant rana
  • 4,509
  • 6
  • 32
  • 72
  • 1
    Well, ok, but then you actually do not have an answer to your original question. Maybe you can edit the question in way that your answer makes sense. – Martin Prikryl Aug 30 '19 at 05:48
  • @ Martin Prikryl.. I agree. I will edit the question. My approach was not correct to loop the file names and then make separated sftp connection for that. I liked your approach which is more efficient and faster. This has changed the whole context of question and answer. :-) I will raise an another question to discuss further issues related to file handling. Many Thanks once again. – vikrant rana Aug 30 '19 at 06:27