Here I am, again sharing some more knowledge on the Cloud Integration topic. This blog is about achieving files, not in the Agent server but in the SFTP folder! Are you excited? Let’s continue then.
Introduction:
When you really need to archive the files? This is a valid question when you are using the file as Sources to your Interfaces. Normally, which ever program generates this file will overwrite it, correct? But what if there is a timestamp associated with the file name? Here, the file name changes in every extraction, and it will create multiple files in the folder. Or sometimes customer wants to archive it for error handling checks. Whatever be the reason, it is possible with WinSCP tool. Is there a straight way to do this from Post load script? You can explore on that, but this is a workaround.
Prerequisite:
You need the Agent active and running, SFTP connections details and a valid credentials, SFTP datastore created and tested and WinSCP tool is installed in the Agent server. A folder created in the Agent server which is added in the Whitelisted Directories. You can check my other blog here to learn how to create an SFTP datastore.
Read the file with timestamp:
First, how will you read a file which has a timestamp associated with the name? Normally, we give the filename as it is, and interface will read the file. But when the filename has a timestamp, it changes in each extraction, and we don’t know it. The solution is, create a global variable and use a wildcard character in the name and we use * for this.
For example, let the filename be Location_20221129103000.csv. Create a global variable $G_FILENAME and assign the value in the Preload script as ‘Location_*.csv’ and use it in the data flow. This will read the Location_20221129103000.csv file and load the data. But there is a problem associated with this. Since the timestamp is different in each extraction, it create multiple files with name starting as ‘Location_’ i.e., Location_20221129103000.csv in the first run and Location_20221129103500.csv in the second run and so on. So, when you run the interface, it will pick up all the files which starts with ‘Location_’ since we use wildcard in the name and end up loading the same data again with the new file. So, in this case archiving is a must. Let’s see how to do that in the below sections.
Archive the files:
I have already provided the link on how to create the SFTP datastores in the Prerequisite section. Please follow that and create the datastores. Then create your interface based on the requirement. Since you use the SFTP folder as Source, you already be using a tool like WinSCP or Cyberduck to access these folders. Create the Archived folder in the SFTP server preferably in the same folder. Here you can archive individual files or a bunch of files in to same or different folders. Here I am explaining how to use WinSCP tool to archive a single file where the Agent is installed in a Windows server and the SFTP folder is in a Linux server.
First, go to the Agent server and create a folder called ‘File_Archive’ and this folder should be whitelisted. This is the place where you are going to keep your archiving codes. You need to create two files, the first one is to write the code to move the file to the Archived folder and the second one is to call this first file using WinSCP tool.
- The first file is a txt file, and you can name it as per your interface. Fill it up with the below code and replace the texts in <> with the SFTP details.
# Connect to SFTP server using a password
open sftp://<user>:<password>@<SFTP server name> -hostkey=”ssh-rsa 2048 <Host key Fingerprint>”
# Move file
mv <File name with folder path> <Archived folder path>
# Exit WinSCP
exit
A simple example will be as follows.
open sftp://user:password@example.com -hostkey=”ssh-rsa 2048 xxxxxxxxxxx…”
Since we have timestamp in the filename, you can use the wildcard character in the filename.
- The second file is a bat file, and name it like the first file. Fill it up with the below code and replace the texts in <> with the SFTP details.
@echo off
winscp.com /script=<First file name.txt with folder path>
Since you are keeping this file in a different folder other than the WinSCP installation folder, include the folder path of WinSCP installation folder like “C:Program Files (x86)WinSCPwinscp.com” in double quotes and there shouldn’t be a space after the /script=.
Please note, here the Agent is installed in a Windows server. If the Agent is installed in a Linux server, you need to make the changes for that especially the backslash.
Now you have created both the txt and bat files in the Agent server, the next step is to call them from the Post load script of the interface.
Use the below script to execute the bat file in the Post load Script.
$G_STATUS = exec(‘bat file name with the folder path’, ‘ ‘, 8);
Here we use the Flag value as 8 so that it returns the concatenation of the return code and the standard error.
Yes, you have completed the steps to archive the files in SFTP folder and now run your interface and test whether the archiving is working. If you use the WinSCP tool to access the files, refresh the folder if the file is still showing there.
Conclusion:
After reading this blog, you will be able to archive the files in SFTP folder and implement this in your integration. Please keep in mind that this is not a solution, this is a workaround. Hope you like this blog! Please share your thoughts, questions and feedback on this topic and follow my profile for exciting blogs on Cloud Integration for Data Services.
Please follow the links to get similar exciting blogs and Q&A .
