September 4, 2013 at 8:21 am
Hi,
How to find date/time last SSIS packgae ran.
Please let me know if you need any other details ..
Thanks In Advance,
-Krishna
September 5, 2013 at 2:55 pm
You won't find that information in the package itself. If you have logging set up on the package, the logging information will give you that information. If you're using the SQL Server Agent to execute the package, you'll find the execution information in the Agent log (although it's still possible that the package could still have been executed outside of SQL Server Agent).
If you're using SSIS 2012 in catalog deployment mode, you can check the SSIS catalog tables for execution information.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
September 5, 2013 at 2:59 pm
Tim Mitchell (9/5/2013)
You won't find that information in the package itself. If you have logging set up on the package, the logging information will give you that information. If you're using the SQL Server Agent to execute the package, you'll find the execution information in the Agent log (although it's still possible that the package could still have been executed outside of SQL Server Agent).If you're using SSIS 2012 in catalog deployment mode, you can check the SSIS catalog tables for execution information.
And if you really want to be thorough, you can combine all three! 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 6, 2013 at 2:32 am
Thank you for your reply...
-krishna
September 9, 2013 at 12:40 am
Koen Verbeeck (9/5/2013)
Tim Mitchell (9/5/2013)
You won't find that information in the package itself. If you have logging set up on the package, the logging information will give you that information. If you're using the SQL Server Agent to execute the package, you'll find the execution information in the Agent log (although it's still possible that the package could still have been executed outside of SQL Server Agent).If you're using SSIS 2012 in catalog deployment mode, you can check the SSIS catalog tables for execution information.
And if you really want to be thorough, you can combine all three! 😀
It sounds over the top, but I do quite often check all three!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 11, 2013 at 2:30 pm
We like to have that info right in the warehouse table and I have a morning job that checks for stale data and alerts us. Pkgs can run successfully yet not refresh the data so we prefer the data to tell us last successful run.
I have a variable in each package that I set to GetDate() , then assign that variable to a field to output with the data.
This has eliminated all guesswork on the part of the users - we show last refresh date on charts in the dashboard so they know As Of immediately. They can always ask for an update if it's not new enough for their purposes but this heads off the "I was looking for a special case I was investigating, but don't see it here ..." tickets.
Wasn't quite sure of your purpose so put it out there in case you aren't asking "for you".
January 30, 2018 at 3:06 pm
I would like to add to the answers above you can try to run the below query, but like most said this will list out the packages that were executed by the agent: SELECT j.name as JobName, s.step_name, SUBSTRING(s.command, LEN(LEFT(s.command, CHARINDEX ('/', s.command))) + 1, LEN(s.command) - LEN(LEFT(s.command, CHARINDEX ('/', s.command))) - LEN(RIGHT(s.command, LEN(s.command) - CHARINDEX ('/SERVER', s.command))) - 1) as StepCommand, jh.run_status, MAX(jh.run_date) As Last_Run_Date
FROM msdb..sysjobs j Inner join msdb..sysjobhistory jh ON j.job_id = jh.Job_id
INNER JOIN msdb..sysjobsteps s ON j.job_id = s.job_id
WHERE s.subsystem = 'SSIS'
Group BY j.name,s.step_name,s.command,jh.run_status
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply