To homepage To homepage
Solved

Pre-script results in error

  • 23 February 2023
  • 4 replies
  • 46 views
R
Rank

v20.10.36.64

Hi,

I have set up a simple script action as follows to set the LAST_CHANGE_DATETIME column to the ENTRY_DATETIME value if it is NULL.

Script action

 

This works fine when selecting from the table in SSMS:

SSMS results using same script action

The script is set as a pre-script to the ADO.NET transfer on the execute step, but when I execute the table, the following error appears:

Incorrect syntax near 'LAST_CHANGE_DATETIME'.

Can I implement this simple script action as a pre-script? The idea is that I can then use a fully populated LAST_CHANGE_DATETIME column for incremental loads on my ledger table.

Thanks,

Richard

 

icon

Best answer by rory.smith 23 February 2023, 17:41

View original

4 replies

rory.smith
Rank
Userlevel 5
Badge +7

This will not work because you cannot modify source data before loading it into TimeXtender. Why not make two incremental selection rules, one for each field?

rvgfox
Rank
Userlevel 3
Badge +1

@richardray I think that if you use that code in a pre-script, it must be a UPDATE, INSERT or DELETE sentence...

R
Rank

This will not work because you cannot modify source data before loading it into TimeXtender. Why not make two incremental selection rules, one for each field?

Thanks Rory. Will that work in the same way as having the two columns combined, i.e. what I was trying to achieve? I suppose if the primary key is configured correctly it will pick up any records where either date is greater than the previous execution.

rory.smith
Rank
Userlevel 5
Badge +7

It depends very much on how the columns are populated in your source system. This situation occurs in some source systems I have worked on, but I would simply test.

 

If using two rules does not work with your source, another option is to load two sets: one based on incremental field and another on the other and resolve between those two inside your staging area

Reply


Terms and ConditionsCookie settings