In our previous article we have discussed about FTP/SFTP Connector in Informatica Cloud and how it enables to transfer files between local file system and a remote SFTP server. But it also has its limitations like unable to process multiple files at a time, read files based on a pattern and so on.
All these limitations can be overcome using another powerful connector in IICS called File Processor Connector. This connector also provides much more capabilities to work with remote as well as local files compared to FTP/SFTP connector.
2. File Processor Connection
File Processor Connector is an Add-On Connector in Informatica Cloud that lets you securely transfer files from one location to another regardless of size and location.
Following are the operations that can be performed using File Processor Connector:
- Transfer files between local system and SFTP server
- Move files within a local file system
- Copy files within a local file system
- Compress and decompress files within a local file system
- Encrypt and decrypt files within a local file system
- Archive and unarchive files within a local file system
- Rename the local files
3. Creating a File Processor Connection
Since File Processor Connector is not a Native connector, you need to navigate to Add-On Connectors in Administrator. Search for File Processor connector and get the license to use in your Org. This involves paying additional licensing cost (30 days Free Trail available).
Once you got the license, the connector can be configured from Connections tab in Administrator.
The following properties needs to be configured in a File Processor Connection
- Runtime Environment: The name of the runtime environment where you want to run the tasks.
- Source File Directory: The location that contains files you want to transfer.
- Target File Directory: The location where you want to place the transferred files.
- Select File: The files that you want to transfer. The following are the Select File fields available to select a specific file or set of specific files..
- Starts with DatePattern
- Contains Date Pattern
- File Pattern: The pattern of the files that you want to transfer.
Note: File Pattern field is not applicable when you select All from Select File connection property.
- Days Calculation: Use Days calculation to select files that are created or modified before the specified date or after the specified date. Specify the value in terms of days. You cannot specify the value in terms of month and year.
- PassKey: The credentials to connect to SFTP server. For example, you can specify the password and passphrase of the SFTP server as passkey1 and passkey2 values.
4. File Processor Connection File Operations
Once the File Processor connection is created. The following are the supported file operations that can be performed using the File Processor connector in a Source Object of a Mapping or Synchronization task in Informatica Cloud.
5. Retrieving files from a remote SFTP Server into Local System
Perform the following steps to create a mapping to download files from a SFTP server into local system.
1. Create a File Processor connection.
The following image the shows the connection configured to download files from a Linux server into a local machine.
- The location in remote sever from which we want to download the files is configured as /home/informatica/sourcefiles.
- The location in local machine where you want the files to be downloaded is configured as C:\TargetFiles.
- In the connection, the Select File and File Pattern are configured to get the files that Start With the name employees.
- The password to connect to the SFTP server is configured in PassKey1.
2. Create a new mapping in Data Integration. Select the File Processor connector as source connection.
Use the SFTP_GET object to download files from a remote SFTP location to a local file system.
When you select the SFTP_GET as the source object, some default fields would be available under the Fields section of the transformation as shown below.
3. Under Query Options of the Source transformation, enter the filter condition details of hostname, port, password and User ID filter fields to connect to the SFTP server.
Note that password value is entered as PassKey1 which refers to the value in the connection where actual password is entered.
File Processor also supports Public and Private Key based authentication. In order to use a Private Key to connect, use SFTP_SSH2_KeyFile in the filter connection and provide the path of the key in the local system as value.
4. In the target transformation, configure a target file which would store details of all the files that are downloaded from remote server into local system.
5. Validate, Save and run the mapping.
The below image shows the employee files with date value appended in the name are transferred into the local system from SFTP server. There is also the csv file created by mapping available in the local system.
The contents of the sftp_get.csv file are as follows which provides the details of the files transferred.
6. Transferring files from Local System to remote SFTP Server
The process to upload the files from local system into a SFTP server is similar to the process we discussed above. But there are few points that needs to be noted.
There should be a separate File Processor connection created for upload activities. Here the source location should be the local system directory and target location should be a SFTP location.
In the mapping select the SFTP_PUT object and pass the values for hostname, port, password and User ID filter fields to connect to the SFTP server.
Use the SFTP_PUT object to upload files from local file system to a remote SFTP location.
7. Transferring files within a Local File System
The File Processor connector can also be used to transfer files between two different locations within a Local file system. The below image shows a file processor connection that perform file operations between C:\SourceFiles and C:\TargetFiles locations in the local file system. By default it looks for files starting with ‘author’.
In the mapping, select the connection and file operation to perform as the object from the drop down list.
If you wish to work on a particular file which is different from the pattern you specified in the connection, you can use the FileName field to specify the filename and also use LastModDate to specify a date filter adhocly in the mapping.
File Processor connector resolves all the limitations of FTP/SFTP connector and also acts as a better and much powerful alternative if you do not wish to use the scripts to transfer files between local system and SFTP server.
It provides capabilities to perform file operations with in the Local File System. Though these actions could be performed easily using pre/post processing commands, it’s good to have feature and allows tracking of changes done through each load.
The only downside, it is an add-on connector and not a native offering from Informatica. But if you are looking for a no-code solution in your Org to work with files, File Processor connector is your go-to solution.
Subscribe to our Newsletter !!
FTP/SFTP Connection in Informatica Cloud enables you to access source and target files present in a remote sever.
File Processor Connector in IICS lets you securely transfer files from one location to another regardless of size and location.
Microsoft Excel Connector is an Add-On Connector that let you read data from multiple worksheets of an Excel source file.