Retrieve Records Modified After Last Run DateTime: Difference between revisions
No edit summary |
No edit summary |
||
Line 4: | Line 4: | ||
=Set Last Run DateTime= | =Set Last Run DateTime= | ||
Create an After Operation Procedure and run the built in Starfish Function SetLastRunDate. Set Exec When to "Once After Conn". | 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. | ||
<source lang="vb"> | <source lang="vb"> | ||
Sub VBScriptProcedure | Sub VBScriptProcedure | ||
Line 12: | Line 12: | ||
=Filter Origin Based On Last Run DateTime= | =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: | 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: | ||
<source lang="sql"> | |||
SELECT * FROM Contacts WHERE ModifyDate >= '@@VAR:LastRunDate@@' | |||
</source> | |||
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: | |||
<source lang="vb"> | <source lang="vb"> | ||
Function ScriptedVariable | Function ScriptedVariable |
Revision as of 15:12, 12 January 2016
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". 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@@"}}]