I am creating a basic data transfer task using TSQL where I am retrieving certain records from one database that are more recent than a given datetime value, and loading them into another database. This will happen periodically throughout the day.
It's such a small task that SSIS seems like overkill - I want to just use a scheduled task which runs a .sql file.
Where I need guidance is that I need to persist the datetime from the last run of this task, then use this to filter the records next time the task runs. My initial thought is to just store the datetime in a text file, and update (overwrite) it as part of the task each time it runs.
I can read the file in without problems using T-SQL, but writing back out has got me stuck. I've seen plenty of examples which make use of a dynamically-built bcp command, which is then executed using xp_cmdshell. Trouble is, security on the server I'm deploying to precludes the use of xp_cmdshell.
So, my question is, are there other ways to simply write a datetime value to a file using TSQL, or should I be thinking about a different approach?
EDIT: happy to be corrected about SSIS being "overkill"...
