0

I want to read file file_DDMMYYYY.csv from a sftp server and load into target table through informatica Intelligent Cloud Services (IICS) It is going to be a daily job so file name will keep changing to contain today's date

There are two questions :

  1. What is the best way to do it in IICS?
  2. While selecting object in source I can only see the objects present in local path , not the remote sftp path . Any idea how can see remote objects here ?
Maciejg
  • 3,088
  • 1
  • 17
  • 30
Harsh
  • 27
  • 7

2 Answers2

1

Answers below -

What is the best way to do it in IICS?

  1. You can use a Command Task step to run shell scripts or batch commands from a file on the Secure Agent machine. Add it in the beginning or add a pre session command task. This will get the file from remote location and create a file list to process the data.
#parameters - You can change as per your requirements.
localpath=/home/localpath/sftp
remotepath=/home/destination/sftp/
listfile=$localpath/datafile_list.txt

#cleanup local path 
rm -f $localpath/datafile_*

#get file - You need to ensure sftp is set between destination and agent server.
sftp username@host_ip << EOF
get $remotepath/datafile_* $localpath/ 
EOF

#create list
ls -1 $localpath/datafile_* >$listfile
  1. Now in the session, you can mention /home/localpath/sftp/datafile_list.txt as a file list name and process them as indirect file.

While selecting object in source I can only see the objects present in local path , not the remote sftp path . Any idea how can see remote objects here ?

You can not see files in remote location. You need to get the file in local and then only it will be visible in infa.

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
  • Thanks for answering. How about using Data Integration service in informatica ? would it complicate the enitre process ? – Harsh Feb 17 '22 at 05:02
  • I am not very sure but think about a sustainable solution in prod when using two different services - iics and data integ service. – Koushik Roy Feb 17 '22 at 05:26
  • I checked in IICS, when I select SFTP as source connection, there is no option as "file list" in the object type drop down menu. Are you suggesting to copy all files from sftp to local path through that shell script ? – Harsh Feb 21 '22 at 22:06
  • Yes. I will check sftp option later – Koushik Roy Feb 22 '22 at 03:27
0

You can create a mapping with a SFTP connection as a source:

enter image description here

Selecting objects will list the SFTP files - it works just fine. The issue is you need a dynamic file name, as this will change. So, choose parameter instead:

enter image description here

Next, create the Mapping task, File Listener that will use your SFTP connection and a Taskflow that will be configured to start on file listener event:

enter image description here

This will automatically add an input field with file information:

enter image description here

All you need to do now is assign the file returned by file listener as input to your mapping:

enter image description here

NOTE: To be more precise: File Listener will return 3 arrays of files:

enter image description here

So you will need to loop over those, if there is more then just one file at a time that you'll get.

Maciejg
  • 3,088
  • 1
  • 17
  • 30