Retrieve Records Modified After Last Run DateTime

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)
	'Sometimes, the Starfish server and the Source Server clocks are not quite in sync, so I subtract 1 minute from my last run datetime to guarantee I don't miss any records.
	gmtLastRun = DateAdd("n", -1, gmtLastRun)
	ScriptedVariable=FormatDate(gmtLastRun, "yyyy-MM-dd HH:mm:ss")
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@@"}}]

    • Related Articles

    • StarfishETL iPaaS Logging Features and Settings

      StarfishETL iPaaS Logging Features and Settings StarfishETL iPaaS has a number of options for logging and monitoring integration activity. Logging is required at various stages of the integration mapping process, during the testing phase prior to ...

    • Run Job Options

      Definitions/explanations of the different options for when you wish to run a job. Option Name Description Begin at Row Begin at Row/Leave blank to start at the first row. End at Row End at Row/Leave blank to process through to the end of all rows. ...

    • Using Row Hashing for Incremental Integrations

      Hashing The Whole Record Typically when setting up an ongoing integration, we only want to pull data from your origin which has changed since the last time Starfish ran. This is accomplished using LastRunDate and applying it as a filter against your ...

    • Scripting Class Object

      From within VBScript operations (ScriptedVariable, VBScriptProcedure, ScriptedField) in additional to normal VBScript functions, the developer has access to a custom “Starfish” class. The tables below define its usage. This class may also be used ...

    • Microsoft SQL Server Connector

      Overview The "SQL Server" Connector pulls data from Microsoft SQL Server. Using rowversion fields Many integrations run frequently and only want to pick up records modified since the last time the integration ran. This can be accomplished with ...