FTP/SFTP connection: Read files from remote server in Informatica Cloud (IICS)

Spread the love

Introduction

Secure File Transfer Protocol (SFTP) is a secure method for transferring files between two computers. FTP is the traditional file transfer protocol and is a basic way of using the Internet to share files. SFTP is an alternative to FTP that also allows you to transfer files, but adds a layer of security to the process.

There are several SFTP clients (ex: WinSCP, Putty, FileZilla) available on the internet that enable you to connect from your local machine to a remote server and transfer data between the two.

  • You must specify the host name, port number, username and password to connect.
  • The host name is the server’s Web address and the port number varies depending on the file protocol you choose.
  • Your username and password must be registered with the server before you can log in.

The Flat File connection available in Informatica Cloud Data Integration enable you to read, access and create flat files. But the files must be present in the same runtime environment (local sever) where your secure agent is installed. Hence the Flat File connection is not suitable to read and create files in a remote SFTP server.

FTP/SFTP Connection

FTP/SFTP Connection in Informatica Cloud is a native connector which enables you to access source and target files present in a remote sever. The connection uses secure protocols, such as SSH, to access the files.

The following properties needs to be configured in a FTP/SFTP Connection

  • Runtime Environment: Runtime environment that contains the Secure Agent to use to access the files.
  • User Name: User name used to log in to the FTP server.
  • Password: Password for the user name used to log in to the FTP server.
  • Host: Host name or IP address of the FTP/SFTP host.
  • Port: Network port number used to connect to FTP/SFTP connection. Default port is 21 for FTP and 22 for SFTP.
  • Local Directory: Directory on a local machine where Secure Agent is installed that stores the local reference file.
  • Remote Directory: Directory on the FTP/SFTP host that stores the remote flat file.
  • Date Format: Date format for date fields in the flat file. Default date format is: MM/dd/yyyy HH:mm:ss
  • Code Page: Code page compatible with the system where the source or target flat file resides.
  • This is a Secure FTP Connection: Indicates whether the connection is secure or not secure. Select to create an SFTP connection.

Local and Remote Directory

The FTP/SFTP connection do not let you read the files directly from the remote sever while configuring the source or target transformation. It only lets you read the files from the local directory configured in the connection. The local file must have the file structure and file name which matches with the file you wanted to read or write in the remote server.

Informatica Intelligent Cloud Services validates the file in the local directory, not the remote directory.

The below image shows the FTP/SFTP connection configured to connect to a remote sever with Host name 192.168.0.106.

  • The Local Directory C:\ReferenceFiles is on a Windows machine.
  • The Remote Directory /home/Informatica/sourcefiles is on a Linux machine.
FTP/SFTP Connection in Informatica Cloud
FTP/SFTP Connection in Informatica Cloud

Informatica connects to the remote sever only during the job run time. Hence the Test Connection feature for FTP/SFTP connection is also not supported in Informatica Cloud.

Rules and Guidelines

Consider the following rules and guidelines for FTP/SFTP connections:

  • When you create a data integration task with a FTP/SFTP source connection, the task fails if it does not find any file in the remote directory with the name defined in the local directory.
  • When you create a data integration task with a FTP/SFTP target connection, it creates a target file in remote directory based on the target file defined in the local directory, overwriting the existing file.
  • If metadata in the local file and remote file are different, Informatica Cloud overwrites the metadata of the remote file with the local file at run time.
  • Informatica Intelligent Cloud Services also uses the local file to generate data preview.

Limitations

There are several limitations with the FTP/SFTP connection in Informatica Cloud.

  • It is not possible to configure file patterns using FTP/SFTP connection to read the files from remote directory. Hence it is not possible to read files with dynamic filenames.
  • It is not possible to read multiple files at a time using FTP/SFTP connection. Hence it is not possible to perform Indirect File loading using FTP/SFTP connection.
  • It is not possible to create files with dynamic filenames unless the reference file in the local directory is renamed as per the requirement before every run.

Alternative methods

There are several alternative methods available to overcome the limitations of FTP/SFTP connection.

  • Run a pre/postprocessing script which will download the files from remote directory into the local directory and vice versa.
    • Once the files are available to read from local directory, it is easy to read files using a file pattern or process multiple files at a time.
    • Create files with dynamic filenames in the local directory and then copy them to remote server using the script.
  • Using a File Processor connection, it is possible to download files from remote directory and upload files into remote directory.
  • Creating a Mass Ingestion task. But it requires an additional license.

Subscribe to our Newsletter !!

Related Articles

Leave a Comment

Related Posts