Skip to main content
  • EN
Hi all,

 

 

FIgured I'd ping this forum before looking elsewhere, but here's a question:

 

 

In an effort to provide data to the enterprise, we'd like to let them know when tables they are pulling were last updated. They will be pulling tables from SQL Server.

 

 

Is it possible to either write or update rows in a 'data status' table, or is there another method out there of creating/providing this data?

 

 

Thanks!!!

 

 

-m
Hi,

 

 

The TimeStamper transformer creates a datetime string representing current time. How about writing it to the table when updating the database?

 

 

Takashi
Hi,

 

 

another option is to have a "metadata" table that contains the tablename and a timestamp for the last update. Like so:

 

 

 

 

David
David, That metadata table would be perfect - is that created using TimeStamper transformer mentioned by Takashi?

 

 

Thanks to you both!
I've been semi-successful at getting this going.

 

 

What I can't figure out is how to connect the TimeStamper to the 'end of a process', rather than a reader.

 

 

What this means for me right now is I'm able to write the table name (via a constant) and date (via timestamper) to a 'status' table, but because I am connecting it to a reader, I'm writing out a record in the status table for every record in the reader table.

 

 

The question is how to only write 1 record to the 'status' table, as implied in David's answer above, so when the update process is complete, the timestamper takes the table name (from the constant) and the date, and writes *one record* to the status table.

 

 

Thanks!!!
One of possible ways is to add an independent feature flow beginning with a Creator. For example:

 

Creator --> TimeStamper --> SQLExecutor (update "status" table)
Takashi, that worked great! Here's the workflow and the result table...

 

 

 

Thank you all for your help!!! I think this will work great!
Another method is to use the 'SQL Statement to execute after translation' option in the 'advanced' parameters of the writer. Here I can use the following statement to write to the UpdateStatus table I've created:

 

 insert into dGisPlanProd].esde].eUpdateStatus] (TableName, LastUpdated) values ('BirthData_AllYears', GETDATE());
 This works like a charm!

 

how would you bring the last_updated column as first column (Column A).  I have something like that but i like to see the date to be displayed in first column.
Rubal, in the image above, the writer I have called "SQL Table", you should be able to open it up and just re-arrange the columns. You might have to 'drop' and re-create the tables after that...
thank you

Reply


Community Stats

30,108
Posts
114,295
Replies
37,744
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.