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:
- Set the last time Starfish ran.
- 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".
Sub VBScriptProcedure If PreviewMode <> True Then SetLastRunDate End If 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. 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@@"}}]