How can I use SFMC automation studio to download a csv from a site into the import ftp folder and import that csv into a data extension?
Do I need to use SJSS? Or is there an activity that does that that I'm missing?
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It only takes a minute to sign up.
Sign up to join this communityHow can I use SFMC automation studio to download a csv from a site into the import ftp folder and import that csv into a data extension?
Do I need to use SJSS? Or is there an activity that does that that I'm missing?
So finally worked this out was working with Salesforce support for quite a while on this.
Create a location for the external sftp https://help.salesforce.com/articleView?id=mc_overview_create_file_transfer_location.htm&type=5
A file transfer activity can download the file. However, it's not clearly documented that it downloads this into the safehouse by default.
In theory you can use another file transfer activity to move this out of the safehouse to e.g. the import folder, however due to a bug, at the moment this is not possible.
The best bet is to use a file import activity to import the file from the safehouse. Because there's no visibility into the safehouse, you must know the file exists there beforehand and ensure you get the filename pattern correct.
Be careful with paths, I was downloading a zip from an sftp like host/folder/archive.zip
. Apparently after unzipped into the safehouse, the file is unzipped to folder/file.csv
So I had to change the host from e.g. 127.0.0.1
to 127.0.0.1/folder
, the import file activity doesn't accept slashes in the filename pattern.
If you can get the site to push the file to SFMC's eFTP, you will then be able to use import file activity followed by file extract activity into a DE