May 10, 2012 at 6:37 pm
i need to record the last successful job run date in a table. As soon as the job is completed I want to update the the last run date column a table with getdate() so that it records the date right after the job is successful. I need to use this date column to fetch in the next run. However, I can't think of way on how to update the date column right after the package is done running.Please advise on workaround?
May 10, 2012 at 7:27 pm
Simple, after the imports and before the package completes, use an Execute SQL Task to update the last rundate in the table.
May 10, 2012 at 9:27 pm
Lynn Pettis (5/10/2012)
Simple, after the imports and before the package completes, use an Execute SQL Task to update the last rundate in the table.
Well honestly, i thought of it too but then that isn't really " after the package completes" is it? What if if something goes wrong in the package after the date is recorded? One more thing,Is there any package property that remains constant even after when it is deployed and scheduled as a job?
May 10, 2012 at 9:59 pm
NevaMind (5/10/2012)
Lynn Pettis (5/10/2012)
Simple, after the imports and before the package completes, use an Execute SQL Task to update the last rundate in the table.Well honestly, i thought of it too but then that isn't really " after the package completes" is it? What if if something goes wrong in the package after the date is recorded? One more thing,Is there any package property that remains constant even after when it is deployed and scheduled as a job?
I'm sorry, but that is exactly what I have done in the SSIS packages that I have setup for transfering data. If the last thing the package does is update the last rundate before exiting, what else could go wrong?
May 11, 2012 at 3:05 pm
i need to record the last successful job run date in a table.
For an actual SQL job, SQL itself records that info.
Look in msdb.dbo.sysjobhistory.
To get the job name, join to msdb.dbo.sysjobs.
Also, make sure you set the SQL Server Agent history to retain the job history for as long as you would ever need it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply