How to send a csv file using SFTP IFS CONNCET from a location in database server

What sort of access do you have? Can you do customizations/custom packages?

If you don’t have any of that, the easiest way is to make sure the database server folder where the file server is located is accessible through file/ftp/sftp, make a READER to poll, read and grab the file, and then apply an outbound routing rule to transfer it to a different server.

There is no schedule for that however, readers poll their target location every 30 seconds.

Hi Simon,

If we want to do customizations/custom packages we have to ask from IFS as we cannot do that by ourselves. I would like to know more details about that option as well. So that we can decide what option is suitable for us.

According to your other option, do we need to use SFTP READER and SFTP SENDER both? can you please explain the steps more clearly?

If we create routing rule how can we execute it? “readers poll their target location every 30 seconds” is this happened automatically?

4.How can I give the specific database server location in routing rule to pick to file from it?

5.from where normally file is picked from to send it using sftp connection IFS Connect in routing rule?

Thanks,

Sachini

Ok so, in short:

1. Readers (whether file reader or ftp reader or sftp reader etc) will try to read the file(s) at the target location (as configured on each reader).
2. They do that every 30 seconds. It is automatic, and cannot be modified. 
3. The file they read will be deleted or archived on the server they’re read from, again depending on the configuration
4. When a file is found and read, the reader will grab the file and create an application message with the file contents in its body
5. A routing rule can then be created, mapped to a routing address that uses a SENDER (again, file, ftp, sftp, mail, your choice really) so that the application message, when created, transfers the file to a desired destination

If the file is in the database server, typically it’s going to be in the FTP repository that IFS puts at your disposal (typically the one used for data migration) but you’d need to confirm that.

If the file is indeed present in the ftp (which is essentially just a way to a folder within the database server file), then you can use an ftp reader to read from your own database server, and then use, for example, and sftp sender to transfer that file somewhere else.

So the first thing you’d want to make sure here is do you have your IFS ftp credentials (if you are a Managed Cloud Customer) or can you set up an FTP server on the folder the file currently is (if you a Remote Customer).

If you don’t have your ftp credentials and you are a managed cloud customer, you should ask IFS for them as they typically provision it for you and provide you the credentials when your environments are first set up.

Hi,

Thanks for the reply. will try this. 

 ​@sachini  can you share and example of how you setup migration job with IAL. i have a similar requirement i.e send a csv file from IFS to FTP server.

We are IFS hosted cloud customer so can i directly send the csv to FTP skipping the step to send it to database server?

​@SimonTestard do you have an example to share how to create a file for the reader to read. i can write code to create an application message but i need pointer in the right direction. I understand routing rules and addresses from EDI side of it. how can i send a quick report/queried data for IFS reader to read.
Appreciate your help.

​@Ukhalid You don’t code anything for the Readers, you just configure a reader (whichever type you want, FILE, FTP, SFTP, etc), which will poll your destination every 30 seconds and, if the file is found in the target, delete or archive the file on the target, and create an application message with the file in its body.

That application message can then be rerouted through routing rules and addresses to forward the document that was pulled from the target towards another destination though a SENDER

​@SimonTestard Thanks.
i guess i need some understanding of how to send my queried data to a location from where Reader can read.

i write a CREATE_OUTPUT_FILE, ‘On Client’ migration job and scheduled it, job ran successfully, where can  i find the downloaded csv.

          or if there ‘s another way of doing this step, like using External file  transfer etc...

Normally you wouldn’t use an output mig job “on client”, that does seem weird, you’d want the file you’d set it to OnServer to a location that’s actually an Oracle Directory.

These Oracle Directories represent folders in the Database Server. These folders are typically made the root of an FTP Server, with IFS (if you’re Cloud Managed) providing you with the FTP Credentials (host, port, username, password).

These FTP can be accessed from any machine that is part of a network where a VPN Tunnel is established between your IFS cloud Management environments and your own corporate network, but they can also be accessed by Connect Readers/Senders, such as an FTP Reader.

That FTP reader can be configured to read files from those locations, and therefore, after your mig job is run and creates the output file on the database server, your ftp reader can poll the ftp location, see the file, and “grab it”, automatically creating an application message with that file in its body.

That application message can then be rerouted through routing rules to forward the file to any other desired location using any Sender you want (FTP, SFTP, etc.)

Thanks ​@SimonTestard 

could you tell how to use IAL as the source in the ‘Database Information’ tab. I tried ifsinfo.filename, ifsinfo.filename_ial, used IFSINFO in the source owner as well. i couldn’t get data in the file.

Never quite tested it with IALs but I’d assume you do it like this:

Where Source Owner is IFSINFO, and the SourceName would be the IAL Name (basically the view that’s being queried, which belongs to IFSINFO).

I wouldn’t use “ifsinfo.xxxx” in the source_name, you shouldnt provide the owner name in the sourcename itself.

Do you get any sort of error when you try to execute the job online (Start Online) ?

Yeah, did it as a test (created an IAL for that purpose that just does a select * from person_info), does seem to work fine:

This is set to On Client just to test it, but it’d work OnServer as well

i got my issue. i was reusing a job that has ‘Source Mapping’ from a view. I created new job and mapped only the fields available in the ial and now it worked.

thanks for your help. Appreciate it.