SSIS – Package duration time
Just a quick blog post today.
I wanted to see how long all my packages were taking to run in SSIS on SQL Server 2012, due to there being some storage issues, which related to the jobs taking longer to run.
So below is the query that I used to pass my parent package, so that I could then get back all the child packages.
Select
Distinct (EX.executable_id)
,executable_name
,(EXS.execution_duration/1000)asDurationInSeconds
,CONVERT(TIME,dateadd(ms,EXS.execution_duration,’01-01-1900′))asDurationInTime
,convert(Date,EXS.start_time)asExecutionDate
,SUBSTRING(convert(varchar(23),EXS.start_time),12,2)asStartHour
,Ex.package_name
FROM[internal].[executables]asEXwith (nolock)
Innerjoininternal.executable_statisticsasEXSwith (nolock)
onEX.executable_id=EXS.executable_id
whereEx.package_name=‘SCOM Parent Package.dtsx’
NOTE: Just change the Ex.Package_Name to your package you want to view the SSIS history for.
In my next blog Post I will show you how to use Power Query and by passing a parameter to then view the output of your query.
Select Distinct (EX.executable_id),
executable_name,
(EXS.execution_duration/1000) asDurationInSeconds,
CONVERT(TIME,dateadd(ms,EXS.execution_duration,’01-01-1900′))asDurationInTime,
convert(Date, EXS.start_time) as ExecutionDate,
SUBSTRING(convert(varchar(23),EXS.start_time),12,2)asStartHour,
Ex.package_name
FROM internal.executables as EX with (nolock) Inner join internal.executable_statistics as EXS with (nolock)
on EX.executable_id = EXS.executable_id
where Ex.package_name = ‘SCOM Parent Package.dtsx’
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘internal.executables’.
Hi there
It appears that it the version of SSIS or the table name is different.
Please check the version: Microsoft SQL Server 2012 (SP3) (KB3072779) – 11.0.6020.0 (X64)
Oct 20 2015 15:36:27
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
Hi there, I would suggest checking that the view/table actually exists in the SSISDB database?
Nope, both these tables (internal.executables / internal.executable_statistics) are not present
Interesting they should be there. I just had a look on my SQL Server 2012 Instance and they are there.
Can you let me know how to check? I tried to query those tables –
use master
go
select * from internal.executables;
Hi there, if you go into your SSISDB under databases, then expand Tables, you should see the tables there
Sorry for troubling you. Yes, I found them now..Will the above query works only when a package is being run?
Ok great. Yes you would need some data to get it showing.
Thanks so much for all the help.. You’re doing an awesome job, your blogs are helping ppl like me very much..
Glad you got it working