Import is not writing over the original Data Extension

Question

I have an import that is based on a SF report, import is set to overwrite. If the new report results are empty, the original information remains in the Data Extension.

I need it to remove any prior information and write over with the new results from the report. What am I missing?

Answer 1

for the following approach you need the date field that indicates the change in the report for processing in the Marketing Cloud. The solution will use the Verification Activity in Automation Studio to alert you and abort the process before the send. It is also based on the assumption that the campaign level date field will always cause both imports to be "empty" if there has been no change within the last 7 days with the result that you only need to check one of the data extensions.

1. Get the field in the report(s) if not already there.

2. Create a Filter or Query activity (which one is best depends on performance requirements and technical prowess) to write records that are older than your date range into another data extension

   2.1 Filter: Create a filter for one of the import data extensions that searches for all records where the date is older than 7 days ([Your datefield] less than today -7). If it returns results as desired create an acitivity for it that you can use in an automation.

   2.2 Query: Create a data extension with two fields ([Your Lead/Contact Id field] and [Your date field]) as target data extension for your query. Then create a Query Activity after the following setup that writes to this data extension:

   SELECT [Your Lead/Contact Id field], [Your date field] FROM [Your import data extension] WHERE datediff(day,[Your date field],getdate()) > 7

3. Setup your automation with the following sequence

   3.1 Lead Report Import, Contact Report Import

   3.2 Filter or Query activity

   3.3 Verification Activity that checks the target data extension of your filter or query for the following condition: Record count is greater than 0. As trigger actions select Stop Automation and Send Email Notification to know that the condition was met and no mails will be sent out.

   3.4 Send Activities for Leads and Contacts

The automation now runs the import, extracts records that are older than your desired date range and aborts before the send activities if it finds one or more records.

Would that work for you?

(1) Documentation, Verification Activity: http://help.marketingcloud.com/en/documentation/automation_studio/using_automation_studio_activities/verification_activity/

Answer 2

From my experience for this scenario, I would clear all records in the data extension before doing the import activity. Hence you can start a fresh import.

You can clear records in the DE using Script activity with WSProxy like this:

<script runat="server">
Platform.Load("core", "1.1.1");

var prox = new Script.Util.WSProxy();
var customerKey = "Your DE cust key";
var action = "ClearData"

var result = prox.performItem('DataExtension', {"CustomerKey": customerKey}, action);
</script>

The thing I love in this way is that it will work even if the DE has a non nullable column.

And then put the script activity before the import activity in an automation

For example: