Retrieve Records Modified After Last Run DateTime

To only run data modified after the last time Starfish was run, we need to do two things:

1. Set the last time Starfish ran.
2. Filter the origin based on the last time Starfish ran.

Set Last Run DateTime

Create an After Operation Procedure and run the built in Starfish Function SetLastRunDate. Set Exec When to "Once After Conn". It is necessary to add this, otherwise the last successful run date will not get set.

Sub VBScriptProcedure
	SetLastRunDate
End Sub

Filter Origin Based On Last Run DateTime

First you must retrieve the DateTime your job was last run. This can be inserted directly into the Origin Filter by using the @@VAR:LastRunDate@@ variable. An example of that in a SQL origin would look something like this:

SELECT * FROM Contacts WHERE ModifyDate >= '@@VAR:LastRunDate@@'

In many applications, DateTimes are converted to GMT in database, so you must convert your last run DateTime to GMT before inserting it into your Origin Filter. To do this, Run a Before Operation set to Exec When "Once Before Conn". You could create any number of functions, but this is the one I've used:

Function ScriptedVariable
	Dim gmtLastRun
	gmtLastRun = CDate("@@VAR:LastRunDate@@")
	gmtLastRun = DateAdd("h", 7, gmtLastRun)
'LogMessage "@@VAR:LastRunDate@@"
	ScriptedVariable=iso_date(gmtLastRun)
End Function

The above function uses a VBScript Global function, iso_date. Add this to your map with the VBScript Global button on the Mapping Tab. You will want to format your date based on whatever origin system you are attempting to filter.

function iso_date(byval dt)
dim y: y = year(dt)
dim m: m=month(dt)
dim d: d=day(dt)
dim h: h=hour(dt)
dim n: n=minute(dt)
dim s: s=second(dt)

  if m < 10 then m="0" & m
  if d < 10 then d="0" & d
  if h < 10 then h="0" & h
  if n < 10 then n="0" & n
  if s < 10 then s="0" & s
  
  iso_date = y & "-" & m & "-" & d & " " & h & ":" & n & ":" & s
end function

Then insert the resulting date into your Origin Filter:

cast(integration_modify_date_c as datetime) > '@@VAR:GMTLastRunDate@@'

or

[{"date_modified":{"$gte":"@@VAR:GMTLastRunDate@@"}}]