February 21, 2017 at 5:03 pm
Good Afternoon Guys,
I am currently having an issue calculating the execution time of a package inside SSIS. Currently i am using this query to insert into my Custom Package Execution Log table
INSERT INTO [dbo].[PACKAGE_EXECUTION_LOG](
[PACKAGE_NAME]
,[PACKAGE_STATUS]
,[LAST_RUN_DATETIME]
,[LAST_UPDATE_DATETIME]
,[EXECUTION_DURATION] ) VALUES (
(Package Name Passed From SSIS)?
,'SUCCESS'
,GETDATE()
,GETDATE()
,DATEADD(SECOND, - DATEDIFF(SECOND, CONVERT(time,GETDATE()), CONVERT(time,(Date Time that was declared as a Variable in SSIS which represents Current Date and Time)?)), CONVERT(time,'00:00'))
)
the results that i am getting is not consistent to how long the package really ran
For Example my package Clocked for 00:15:23 (Fifteen minutes and 23 seconds)
but the execution time that is being inserted is 05:51:18 (5 hours 51 Minutes and 18 seconds)
Could you review my code and see where i missed from my part? any help is greatly appreciate
Best Regards,
Noel
February 22, 2017 at 2:24 am
You could simplify your SQL, for example:
declare @start datetime;
set @start = '20170101 09:00:00';
declare @end datetime;
set @end = '20170101 09:15:28';
select convert(varchar(8), @end - @start, 108);
When capturing start and end times I prefer using a script task in each case and passing the value back to an SSIS variable so the final line above in your SQL Task would be:
select convert(varchar(8), ? - ?, 108);
Where you map the values of ? and ? using parameters.
February 22, 2017 at 7:39 am
Are you sure that you're using the correct value from @[System::StartTime] ?
Why do you have the negative mark instead of fixing the DATEDIFF parameters?
DECLARE @StartTime datetime = ? /*@[System::StartTime] as parameter*/
SELECT DATEADD(SECOND, DATEDIFF(SECOND, @StartTime, GETDATE()), CONVERT(time,'00:00'))
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply