Retrieve Records Modified After Last Run DateTime: Difference between revisions
Jkuehlthau (talk | contribs) (Created page with "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 r...") |
Jkuehlthau (talk | contribs) No edit summary |
||
Line 21: | Line 21: | ||
ScriptedVariable=iso_date(gmtLastRun) | ScriptedVariable=iso_date(gmtLastRun) | ||
End Function | End Function | ||
</source> | |||
The above function uses a VBScript Global function, iso_date. Add this to your map with the VBScript Global button on the Mapping Tab. | |||
<source lang="vb"> | |||
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 | |||
</source> | </source> | ||
Revision as of 21:41, 27 July 2015
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 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. 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.
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:LastRunDate@@'