2

We have an automation to export the people in a Data Extension with the LastDeliveryDate value equal to 21 days ago. The file will end up on an SFTP server that is not Salesforce's own Enhanced server. The automation flow works as it should and we receive the file on the SFTP server. The only thing that does not make sense is that if we run the automation early in the morning the filename (containing wildcards) is one day behind and the data inside the files are also one day behind.

E.g.

  1. When we run the automation on 2017-09-04 at 10 AM, the file is generated as Aktivering_dag21_2017-09-04.csv
  2. When we run the automation on 2017-09-04 at 6 AM, the file is generated as Aktivering_dag21_2017-09-03.csv which is one day old.

Now, I try to elaborate as much as I can on the automation.

The automation consists of 4 activities (in order):

  1. A Filter Activity connected to the Data Filter with condition LastDeliveryDate is equal to Today - 21 Days
  2. An Sql Query Activity that joins the data in (1) to another data extension to receive columns from both. The results update another data extension we call DE_21_Days.
  3. A Data Extract Activity with file naming pattern of Aktivering_dag21_%%Year%%-%%Month%%-%%Day%%.csv
  4. A File Transfer Activity with the following properties- File naming pattern: Aktivering_dag21_%%Year%%-%%Month%%-%%Day%%.csv, Use Local time zone setting: Checked (have tested with Unchecked as well), File Action: Move a File From Safehouse, Destination: the external ftp server mentioned above.

Mind that the timezone for the logged in user as well as the BU is the same timezone which is (GMT+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna.

But the timezone for the external server is apparently set to Spanish timezone. I'm not sure if this could be a problem because I suppose the generation of file and the naming of the file happens before it is landed on the server.

Any hint regarding why the file contains data that is one day older that it is supposed to is appreciated.

  • Hi, @disasterkid, I am facing the exact issue. Did you find any solution to this? – Praveen Kumar Bandi Oct 23 '17 at 8:37
  • Hi @PraveenKumarBandi, no unfortunately this is still a mystery. I have contacted SF support but their reply is not much of a help since they want me to simulate the situation for them and they are not able to replicate the error. – Disasterkid Oct 23 '17 at 9:01
  • as far as I am aware, the data filter and the query activity use datetiome fields in the server timezone. In the data filter, make sure you selected the "use local time zone setting' and in the query activity you need to convert the datetime to your timezone using DateADD. Example : DATEADD(hh,1,EventDate) . The file transfer activity itself transfers the file in the server date time format. I have raised this with support and I will let you know if they have any resolution for this. – Praveen Kumar Bandi Oct 23 '17 at 9:47
  • @PraveenKumarBandi That would be fantastic. I have actually tried "Use Local Time Zone Setting" to no avail but the DATEADD problem I should also give a try. This is a very hidden error but has kept us and the customer wondering for a long time. – Disasterkid Oct 23 '17 at 10:52
  • Agree with @PraveenKumarBandi - being in NZ, it's easy to see how server time is applied to everything. On a side note, when using import activities, it imports the latest file based on the modified date in the FTP, not just on the %%[date personaliztion]%%.csv – Christopher Carswell Feb 28 '18 at 3:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.