Getting only changed records in ongoing integration

ATTENTION: This forum is no longer active. Please navigate to our new support site at https://support.starfishetl.com/
Viewing 3 posts (of 3 total)
bsegers
User - Author
Post count: 1
#1

I need to do an ongoing integration with SalesLogix to SalesLogix probably SDATA on both sides.
Is it possible for me to inject “Last RunDateTime” in my Origin Select or can I write back to the origin DB once a record is changed so I can filter from my next select?

Comments (3)

Aron Hoekstra's picture Aron Hoekstra
Administrator
Post count: 2
#2

Yes, the best way to do this now is to read the date from a file, and write it back out at the end. If you read it into a variable, you can use it in your Origin SQL statement.

1. First you'll create a Global VBscript variable to hold the date/time of the run launch. Click the Global Include button and enter the following line:
Dim CurrentRunStartDate

2. Then create a new Exec Before operation of type VBScript/Variable. Give it a variable name "vLastRunDate". Set the Exec When property to "Once Before Conn" and paste in the following code:

  1. Function ScriptedVariable
  2. 	Dim fso,tf,str
  3. 	Dim objFile
  4.  
  5. 	CurrentRunStartDate=Now
  6.  
  7. 	Set fso = CreateObject("Scripting.FileSystemObject")
  8. 	Set objFile = fso.GetFile("C:inetpubwwwrootStarfishStarfishEngineLastRunDate.txt")
  9. 	if objFile.Size > 0 then
  10. 		Set tf = fso.OpenTextFile("C:inetpubwwwrootStarfishStarfishEngineLastRunDate.txt", 1, False)
  11. 		str = tf.ReadLine
  12. 		tf.Close
  13. 		Set tf = Nothing
  14. 	else
  15. 	    str = ""
  16. 	end if
  17. 	Set objFile = nothing
  18. 	Set fso = nothing
  19.  
  20. 	ScriptedVariable=str
  21. End Function

3. Now you can reference this variable in your Origin SQL. You could do something like this:
SELECT * FROM Accounts WHERE ModifyDate >= '@@VAR:vLastRunDate@@'

4. The last thing to do is make sure you write the new last run date back out at the end of the job.
Create a new Exec After operation of type VBScript Procedure. Change the Exec When property to "Once After Conn", and paste in the following code:

  1. Sub VBScriptProcedure
  2. 	Dim fso,tf
  3. 	Set fso = CreateObject("Scripting.FileSystemObject")
  4. 	Set tf = fso.OpenTextFile("C:inetpubwwwrootStarfishStarfishEngineLastRunDate.txt", 2, True)
  5. 	tf.WriteLine(CurrentRunStartDate)
  6. 	tf.Close
  7. 	Set tf = nothing
  8. 	Set fso = nothing
  9. End Sub

Let me know if you have any questions.

Log in to post comments
Justin Kuehlthau's picture Justin Kuehlthau
Administrator
Post count: 24
#3

There is a new way to do this.

In version 2.7, a function was added to Starfish to set the last run date of a Job.  See: changelog.txt

In your job Mapping, create an After operation VBScript Procedure of:
Sub VBScriptProcedure
SetLastRunDate
End Sub

Set this Procedure to Exec When: Once After Conn.

In your Origin, set a Filter for:
where CreateUpdateTime > '@@VAR:LastRunDate@@'

Log in to post comments
Justin Kuehlthau's picture Justin Kuehlthau
Administrator
Post count: 24
#4

During my most recent integration I found the datetimes stored in my database were in GMT. I ended up having to convert my @@VAR:LastRunDate@@ variable to a date and then add the correct amount of time to get to GMT. I tried using the convert_tz mysql function, but it was not working with my connector. This ended up working:

cast(integration_modify_date_c as datetime) > Date_Add(str_to_date('@@VAR:LastRunDate@@','%c/%e/%Y %l:%i:%s %p'), INTERVAL 8 HOUR)

Log in to post comments
Viewing 3 posts (of 3 total)

Forum Login

Login or sign up for our forums to connect to the user community.

Reply

You must log in to post.

Not a Member? Register.