0

CSV -> MC SFTP -> MC DE -> SFDC Custom Object

We received a CSV file every day with a complete set of current data from an external system. Approx 16K records, 43 columns, 5MB file size.

This data will be loaded into Salesforce, then compared to current data and then trigger automatic record creation and update, depending on complex conditions.

Also the data needs a lot of processing/transformation and splitting into the various objects.

We want to use the SFTP function in Marketing Cloud (to avoid paying for this functionality via e.g. Dataloader), then load the data into a DE via an automation, then load this data into a Service Cloud custom object, then perform the processing.

Working Already: 1. SFTP file import activity 2. Automation triggered on file drop which loads the data into the MC DE

to be completed: 3. Load data from MC DE into Service Cloud

I thought I would be able to just create an Activity to achieve this but actually, I'm not seeing this option - but I may be missing it.

Alternatively, another solution may be more optimal?

Secondary question: I'm concerned the CSV file import activity doesn't have the option to specify which file it selects. Each day's file has the date in the file name i.e. 20190110 but I can't see an option to specify that the import should operate on today's file. I would be open to deleting the CSV file after import but can't see this option either, although I've read posts here on SE that references this. Options and best practice?

3
  • One small comment on the filename date. (i do not know sfdc). Salesforce Marketing cloud allows Wildcards like %%Day%% or %%Month%% or %%Year%% which will be populated by the current date from the server instance. Maybe this is also available in SFDC? Jan 10, 2019 at 9:54
  • @johannesSchapdick thanks for you input, not sure I quite understand. The wildcards in MC don't allow to specify which file is loaded do they? Also SFDC doesn't include an SFTP hence using the MC SFTP.
    – Foweyboy
    Jan 10, 2019 at 10:01
  • The wildcards allow it, for example if you configure a file import or export activity you can say filename: "Prefix_%%day%%%%month%%%%year%%.csv" then the marketing cloud imports the file with the most recent timestamp that fulfills the condition. When you do it in an export it will write the timestamps accordingly. Jan 10, 2019 at 10:07

2 Answers 2

1

If the CSV has a ContactKey column (lead id or contact id or account id), you can also use Journey Builder Salesforce activities to add a record to a Standard or Custom Object.

  1. Fill the DE with the CSV based on an automation triggered by a file drop
  2. Inject contacts in the DE to a Journey and create a new record in Salesforce
0

I believe SFMC Connector only works 1 way with data transfer (only passes back tracking info to SFDC), so this would not be an option to meet your needs. Communication between SFMC and the rest of the SF suite is not quite to the level it should be just yet - but it is being worked on.

You would likely need to create an API call in a Script Activity to pass this data into SFDC. This can then be added into your automation as an additional step that would then do the following:

  1. Trigger Automation based on file drop
  2. Import file in first step into Data Extension
  3. Create a script activity to gather the DE records from SFMC (SSJS)* and then use an API call (aimed at SFDC, not SFMC) to place these records into your custom object in SFDC.

To answer the second part of your question, you can use a substitution string like %%FILENAME_FROM_TRIGGER%% inside of your import to make sure the import will only pull the file that triggered the automation. (other examples of sub strings you can use). You would then just set the Triggered Automation with wildcards to match the file name you are dropping to ensure it only pulls the specific file. E.g. File_%%year%%%%month%%%%day%% which would look for File_20190110 today and File_20190111 for tomorrow, etc.

*Please note that the docs are incorrect on the LookupOrderedRows SSJS function, the second parameter is the number of rows you are looking up, not the name of the field you wish to retrieve. I believe the max rows you can return is 2500.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .