My name is Mike Hancock, I have been an EIM (Enterprise Information Management) Consultant at NTT DATA Business Solutions for over 10 years and I have been using SAP Data Services for over 15 years.
I decided to write this blog after responding to the question
SAP Data Services - renaming a file on a remote FTP server
Introduction
A while back SAP added File Location objects to Data Services, together with file manipulation functions, primarily I believe to simplify File handling and to remove dependency on DOS commands, batch files and third party applications like WinSCP for SFTP file transfers.
Did they succeed, both in general and specifically in relation to SFTP file Transfer?
The answer is mixed and depends upon your requirements – Yes and No
- Yes: File Handling of Local Files can now be handled much more easily with the Built in capabilities of Data Services, without the need to use DOS commands
- Yes: It is easier, without the need for something like WinSCP, for Getting and Putting files between remote file locations and local directories
- No: File Handling on remote file locations is not possible. You will still need something like WinSCP to move or delete files on a remote file location, such as an SFTP site.
The following sections will cover these aspects of Data Services
- File handling capabilities of Local files
- Transferring files between Local and Remote directories
- Issues with handling files within a Remote set of Directories
- Remote Location Credentials
- References
- Conclusion
Local Files
File Location objects can be used to define local directories and use their configurations to implement different directories for your various environments (Development, UAT and Production).
File Location objects can then be used in the file configurations within a Dataflow to define the locations of the files that need to be processed or created.
The File_Copy, File_Delete and File_Move functions can also be used in scripts to perform your required normal file handling requirements, without the need to use DOS commands.
The Wait_for_File Function not only pauses your job for a specified time period (including zero seconds or indefinitely) till the specified file(s) are created, BUT can also be used to get directory listings into a variable.
- The List of Filenames variable can then be used to process these files one at a time, by stepping through each file one at a time in conjunction with the “While Loop” Workflow.
- The List of Filenames variable can also be used to capture, move, process and report all the files that match the wildcard file definition used in the Wait_for_File Function.
The File_Exists function can be used for example to
- To trigger a process if a file exists; or
- To loop a process until a file does not exist
Remote File Transfers
File Location objects can also be also be used for remote directories, like SFTP, FTP, SCP and various cloud storage locations.
When used in the file configurations within a Dataflow, the File Location object defines both the local and remote directories of the files. When the job executes
- Input Files: Files that need to be read from a remote directory, are first copied to a local directory
- Output Files: Files are written to both the local and remote directories
There is an option to “Delete files after Transfer”, but this only deletes the local copy regardless of whether you are either getting or putting files onto a remote location.
You can also use copy_from_remote_system and copy_to_remote_system functions in scripts to get or put files onto a remote server using the file location object.
Remote File Handling
Whilst Data Services has good capabilities of
- Processing Local Files
- Using Remote / Local File Location Configurations to define different credentials / directories for your various environments
- Simplifying the Transfer of files to/from Remote locations
But, the manipulation of files within a remote location is where it is necessary to resort to using other tools.
There are no functions within Data Services to manipulate files on a remote location, such as getting directory listings, deleting, renaming or moving files within the remote location.
Yes you can copy all the files from a remote location to a local directory and then get the directory listing of the local directory, but you still cannot delete or move (rename) the file on the remote location to stop files from being processed again or move processed files to an archive directory on the remote location.
This might not be a problem if you are dealing with static files, but most likely will be an issue with daily transactional files.
This means you have to use the command line options of something like WinSCP to perform these file handling functions on the remote location.
Therefor using 2 different methodologies to process files on remote locations.
You will also most likely need to get approval to install third party applications like WinSCP, especially as they need to have command line capabilities.
With WinSCP, you can pass the remote server credentials from Data Services to the WinSCP scripts, via parameters - eliminating the security risk of having these credentials in text files.
Remote Location Credentials
Cautionary note: It may take you longer than expected to get remote location credentials and in the correct format, especially if the remote location is run by a third party.
e.g. Hostkey Fingerprints for SFTP can come in different formats and different service packs of Data Services require different formats of Hostkey fingerprints
- The Hostkey fingerprint must be in MD5 or SHA1 algorithm for Data Services 4.2 SP7 through SP12
- The Hostkey fingerprint must be ECDSA Hostkey for Data Services 4.2 SP13 and later.
The following links may assist you in getting the correct keys
References
YouTube video
SAP Data Services: File Location Object will give you a summary of File Location object and related function capabilities.
Use the following SAP Help Portal links for further information on options available
Conclusion
Data Services File Location objects and file handling functions can be used for the majority of your file handling requirements and will simplify and speedup developments and would use them for the majority of your file handling requirements.
You just may find yourself reverting back to third party applications, such as WinSCP, to manipulate files within your remote location.
Please like this post if you found it useful and feel free to provide feedback or thoughts in the comment section