Hi all I have a etl job that runs every day at 2am, and recently i was getting the following error: Message Executed as user: AD\MOSS_BI. ...te Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:00:02 AM Error: 2012-08-01 03:37:36.02 Code: 0x00000000 Source: Get Duration Description: Difference of two datetime columns caused overflow at runtime. End Error Error: 2012-08-01 03:37:36.05 Code: 0xC002F210 Source: Get Duration Execute SQL Task Description: Executing the query "update dbo.FACT_InspectionDetails set SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS( ScheduledDateTime ,RequestedDateTime ,InspectionDateTime )" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:00:02 AM Finished: 3:53:24 AM Elapsed: 6. The step failed. But when i executed the TASK individually (manually0 the container is executed without any hitches. Any idea why its failing? Thanks

The error must be in your UDF FN_GET_INSPECTION_DURATION_SECONDS Specifically when it calculates the SecodsDuration, I guess it uses DATEDIFF and thus this article can help you to recover: http://www.sql-server-performance.com/2007/difference-of-datetime-columns-caused-overflow/ To answer why you do not get the same error running the package manually is because the time was different. Arthur My Blog

Please note that SQL server does not support DateTime smaller than 1900/1/1. Any date time value passed before that will cause an error. If you are using .NET then you should check the date time value that you are passing which can be smaller than allowed by SQL server. Hope this helps-Please mark the post as answered if it answers your question

Thanks Arthur, but i excluded the records which have this bad date, and i didnt quite understand when you said the time was different when i run the package manually. The whole package has many taks in them so this specific taks is in a container and i excluded the records with bad data ( in the sql query that i'm extracting the data from the source), so technically these records are not causing the data over flow. This container executes with no problem, but the error comes when it runs as a scheduled job. Thanks

Thanks DotNetMonster but i have the following data set 2023-04-23 00:00:00.000 2024-05-28 00:00:00.000 2026-04-25 00:00:00.000 2026-07-26 00:00:00.000 2028-10-01 00:00:00.000 2030-09-30 00:00:00.000 2030-12-23 00:00:00.000 2035-03-31 00:00:00.000 2040-04-01 00:00:00.000 2101-07-23 00:00:00.000 2102-04-26 00:00:00.000 3012-05-30 00:00:00.000 5201-01-10 00:00:00.000 8201-01-09 00:00:00.000 8201-06-30 00:00:00.000 i excluded the last 6 records and still getting the same error. I think sql can manage dates until 2099.

Thanks Arthur, but i excluded the records which have this bad date, and i didnt quite understand when you said the time was different when i run the package manually. The whole package has many taks in them so this specific taks is in a container and i excluded the records with bad data ( in the sql query that i'm extracting the data from the source), so technically these records are not causing the data over flow. This container executes with no problem, but the error comes when it runs as a scheduled job. Thanks The time the package failed was not the at the time you ran it locally. E.g. it ran at 1AM and you tested at 9:30AM, this is different. Set your local clock on the PC to match the execution time at the server and try the package then.Arthur My Blog

The function is : set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- ===================================================================================-- Author: BEEKAY-- Create date: 3/3/2010-- Description: Given a unique inspection number, this function returns an integer -- representing the Inspection's duration in seconds. -- ===================================================================================-- ALTER FUNCTION [dbo].[FN_GET_INSPECTION_DURATION_SECONDS]( -- Add the parameters for the function here @serv_prov_code varchar(15), @b1_per_id1 varchar(5), @b1_per_id2 varchar(5), @b1_per_id3 varchar(5), @g6_act_num bigint)RETURNS intASBEGIN --********************************************************************************************** -- Declare the return variable --********************************************************************************************** DECLARE @duration bigint --********************************************************************************************** -- Declare all other local variables --********************************************************************************************** DECLARE @start_datetime datetime DECLARE @start_time1 varchar(10) DECLARE @start_time2 varchar(10) DECLARE @end_datetime datetime DECLARE @end_time1 varchar(10) DECLARE @end_time2 varchar(10) --********************************************************************************************** -- Compute the Starting DateTime --********************************************************************************************** -- Start by using the SCHEDULED DATE select @start_datetime = g6_act_dd, @start_time1 = ltrim(rtrim(g6_act_t1)), @start_time2 = ltrim(rtrim(g6_act_t2)) from [DB].dbo.G6ACTION where REC_STATUS = 'A' and SERV_PROV_CODE = @serv_prov_code and B1_PER_ID1 = @b1_per_id1 and B1_PER_ID2 = @b1_per_id2 and B1_PER_ID3 = @b1_per_id3 and G6_ACT_NUM = @g6_act_num -- Use REQUESTED DATE if not scheduled if @start_datetime is null begin select @start_datetime = g6_rec_dd, @start_time1 = ltrim(rtrim(g6_rec_t1)), @start_time2 = ltrim(rtrim(g6_rec_t2)) from [DB].dbo.G6ACTION where REC_STATUS = 'A' and SERV_PROV_CODE = @serv_prov_code and B1_PER_ID1 = @b1_per_id1 and B1_PER_ID2 = @b1_per_id2 and B1_PER_ID3 = @b1_per_id3 and G6_ACT_NUM = @g6_act_num end -- Determine the appropriate timestamp to use -- (skip this section if there is no StartDate) if @start_datetime is not null begin if datepart(hh,@start_datetime)= 0 and datepart(mi,@start_datetime)= 0 and datepart(ss,@start_datetime)= 0 begin -- extract h/m/s from @start_time2, based on the pattern of the string -- test if in 00:00:00 format if PATINDEX('[0-9][0-9]:[0-9][0-9]:[0-9][0-9]', @start_time2) = 1 and @start_time2 <> '00:00:00' begin -- if 12AM, only add minutes and seconds if left(@start_time2,2) = '12' and @start_time1 = 'AM' begin --set @start_datetime = dateadd(hh, cast(left(@start_time2, 2) as int), @start_datetime) set @start_datetime = dateadd(mi, cast(substring(@start_time2, 4, 2) as int), @start_datetime) set @start_datetime = dateadd(ss, cast(right(@start_time2, 2) as int), @start_datetime) end else begin set @start_datetime = dateadd(hh, cast(left(@start_time2, 2) as int), @start_datetime) set @start_datetime = dateadd(mi, cast(substring(@start_time2, 4, 2) as int), @start_datetime) set @start_datetime = dateadd(ss, cast(right(@start_time2, 2) as int), @start_datetime) end -- if PM, may need to convert to 24-hour clock if @start_time1 = 'PM' and cast(left(@start_time2, 2) as int) < 12 set @start_datetime = dateadd(hh, 12, @start_datetime) end -- test if in 00:00 format else if PATINDEX('[0-9][0-9]:[0-9][0-9]', @start_time2) = 1 and @start_time2 <> '00:00' begin -- if 12AM, only add minutes if (left(@start_time2,2)) = '12' and @start_time1 = 'AM' set @start_datetime = dateadd(mi, cast(substring(@start_time2, 4, 2) as int), @start_datetime) else begin set @start_datetime = dateadd(hh, cast(left(@start_time2, 2) as int), @start_datetime) set @start_datetime = dateadd(mi, cast(right(@start_time2, 2) as int), @start_datetime) end -- if PM, may need to convert to 24-hour clock if @start_time1 = 'PM' and cast(left(@start_time2,2) as int) < 12 set @start_datetime = dateadd(hh, 12, @start_datetime) end -- test if in 0:00 format else if PATINDEX('[0-9]:[0-9][0-9]', @start_time2) = 1 and @start_time2 <> '0:00' begin set @start_datetime = dateadd(hh, cast(left(@start_time2, 1) as int), @start_datetime) set @start_datetime = dateadd(mi, cast(substring(@start_time2, 3, 2) as int), @start_datetime) -- if PM, convert to 24-hour clock if @start_time1 = 'PM' set @start_datetime = dateadd(hh, 12, @start_datetime) end -- otherwise there is no valid timestamp: per DCE, assume 7:30 AM else begin set @start_datetime = dateadd(hh, 7, @start_datetime) set @start_datetime = dateadd(mi, 30, @start_datetime) end end end --********************************************************************************************** -- Compute the Ending DateTime --********************************************************************************************** -- Start by using the Completion Date and Completion Time fields select @end_datetime = g6_compl_dd, @end_time1 = g6_compl_t1, @end_time2 = g6_compl_t2 from [ACCELA].dbo.G6ACTION where REC_STATUS = 'A' and SERV_PROV_CODE = @serv_prov_code and B1_PER_ID1 = @b1_per_id1 and B1_PER_ID2 = @b1_per_id2 and B1_PER_ID3 = @b1_per_id3 and G6_ACT_NUM = @g6_act_num -- Determine the appropriate timestamp to use -- (skip this section if there is no EndDate) if @end_datetime is not null begin if datepart(hh, @end_datetime) = 0 and datepart(mi, @end_datetime) = 0 and datepart(ss, @end_datetime) = 0 begin -- extract h/m/s from @end_time2, based on the pattern of the string -- test if in 00:00:00 format if PATINDEX('[0-9][0-9]:[0-9][0-9]:[0-9][0-9]', @end_time2) = 1 and @end_time2 <> '00:00:00' begin -- if 12AM, only add minutes and seconds if left(@end_time2,2) = '12' and @end_time1 = 'AM' begin --set @end_datetime = dateadd(hh, cast(left(@end_time2, 2) as int), @end_datetime) set @end_datetime = dateadd(mi, cast(substring(@end_time2, 4, 2) as int), @end_datetime) set @end_datetime = dateadd(ss, cast(right(@end_time2, 2) as int), @end_datetime) end else begin set @end_datetime = dateadd(hh, cast(left(@end_time2, 2) as int), @end_datetime) set @end_datetime = dateadd(mi, cast(substring(@end_time2, 4, 2) as int), @end_datetime) set @end_datetime = dateadd(ss, cast(right(@end_time2, 2) as int), @end_datetime) end -- if PM, may need to convert to 24-hour clock if @end_time1 = 'PM' and cast(left(@end_time2, 2) as int) < 12 set @end_datetime = dateadd(hh, 12, @end_datetime) end -- test if in 00:00 format else if PATINDEX('[0-9][0-9]:[0-9][0-9]', @end_time2) = 1 and @end_time2 <> '00:00' begin -- if 12AM, only add minutes if (left(@end_time2,2)) = '12' and @end_time1 = 'AM' set @end_datetime = dateadd(mi, cast(substring(@end_time2, 4, 2) as int), @end_datetime) else begin set @end_datetime = dateadd(hh, cast(left(@end_time2, 2) as int), @end_datetime) set @end_datetime = dateadd(mi, cast(right(@end_time2, 2) as int), @end_datetime) end -- if PM, may need to convert to 24-hour clock if @end_time1 = 'PM' and cast(left(@end_time2,2) as int) < 12 set @end_datetime = dateadd(hh, 12, @end_datetime) end -- test if in 0:00 format else if PATINDEX('[0-9]:[0-9][0-9]', @end_time2) = 1 and @end_time2 <> '0:00' begin set @end_datetime = dateadd(hh, cast(left(@end_time2, 1) as int), @end_datetime) set @end_datetime = dateadd(mi, cast(substring(@end_time2, 3, 2) as int), @end_datetime) -- if PM, convert to 24-hour clock if @end_time1 = 'PM' set @end_datetime = dateadd(hh, 12, @end_datetime) end -- otherwise there is no valid timestamp: per DCE, assume 4:30 PM else begin set @end_datetime = dateadd(hh, 16, @end_datetime) set @end_datetime = dateadd(mi, 30, @end_datetime) end end end --********************************************************************************************** -- Compute the Duration; remove Weekend/Holiday Time --********************************************************************************************** set @duration = datediff(ss,@start_datetime, @end_datetime) if @duration < 0 set @duration = @duration + dbo.FN_GET_BLACKOUT_SECONDS(@start_datetime, @end_datetime) else set @duration = @duration - dbo.FN_GET_BLACKOUT_SECONDS(@start_datetime, @end_datetime) --********************************************************************************************** -- Return the result of the function --********************************************************************************************** RETURN @durationEND And in the control flow, i have s execute sql task with the following query: update dbo.FACT_InspectionDetails set SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS( ScheduledDateTime ,RequestedDateTime ,InspectionDateTime ) And when i tried to excute the task, i'm getting the following error [Execute SQL Task] Error: Executing the query "update dbo.FACT_InspectionDetails set SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS( ScheduledDateTime ,RequestedDateTime ,InspectionDateTime )" failed with the following error: "An insufficient number of arguments were supplied for the procedure or function dbo.FN_GET_INSPECTION_DURATION_SECONDS.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Where in the function am i doing wrong? Please need help.

Thanks Arthurz but this is the problem am facing. I have the following function set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- =================================================================================== -- Author: -- Create date: -- Description: Given a unique inspection number, this function returns an integer -- representing the Inspection's duration in seconds. Follows logic -- originally incorporated into the "48-Hour Report" in Crystal. -- =================================================================================== -- ALTER FUNCTION [dbo].[FN_GET_INSPECTION_DURATION_SECONDS] ( -- Add the parameters for the function here @serv_prov_code varchar(15), @b1_per_id1 varchar(5), @b1_per_id2 varchar(5), @b1_per_id3 varchar(5), @g6_act_num bigint ) RETURNS int AS BEGIN --********************************************************************************************** -- Declare the return variable --********************************************************************************************** DECLARE @duration bigint --********************************************************************************************** -- Declare all other local variables --********************************************************************************************** DECLARE @start_datetime datetime DECLARE @start_time1 varchar(10) DECLARE @start_time2 varchar(10) DECLARE @end_datetime datetime DECLARE @end_time1 varchar(10) DECLARE @end_time2 varchar(10) --********************************************************************************************** -- Compute the Starting DateTime --********************************************************************************************** -- Start by using the SCHEDULED DATE select @start_datetime = g6_act_dd, @start_time1 = ltrim(rtrim(g6_act_t1)), @start_time2 = ltrim(rtrim(g6_act_t2)) from [ACCELA].dbo.G6ACTION where REC_STATUS = 'A' and SERV_PROV_CODE = @serv_prov_code and B1_PER_ID1 = @b1_per_id1 and B1_PER_ID2 = @b1_per_id2 and B1_PER_ID3 = @b1_per_id3 and G6_ACT_NUM = @g6_act_num -- Use REQUESTED DATE if not scheduled if @start_datetime is null begin select @start_datetime = g6_rec_dd, @start_time1 = ltrim(rtrim(g6_rec_t1)), @start_time2 = ltrim(rtrim(g6_rec_t2)) from [ACCELA].dbo.G6ACTION where REC_STATUS = 'A' and SERV_PROV_CODE = @serv_prov_code and B1_PER_ID1 = @b1_per_id1 and B1_PER_ID2 = @b1_per_id2 and B1_PER_ID3 = @b1_per_id3 and G6_ACT_NUM = @g6_act_num end -- Determine the appropriate timestamp to use -- (skip this section if there is no StartDate) if @start_datetime is not null begin if datepart(hh,@start_datetime)= 0 and datepart(mi,@start_datetime)= 0 and datepart(ss,@start_datetime)= 0 begin -- extract h/m/s from @start_time2, based on the pattern of the string -- test if in 00:00:00 format if PATINDEX('[0-9][0-9]:[0-9][0-9]:[0-9][0-9]', @start_time2) = 1 and @start_time2 <> '00:00:00' begin -- if 12AM, only add minutes and seconds if left(@start_time2,2) = '12' and @start_time1 = 'AM' begin --set @start_datetime = dateadd(hh, cast(left(@start_time2, 2) as int), @start_datetime) set @start_datetime = dateadd(mi, cast(substring(@start_time2, 4, 2) as int), @start_datetime) set @start_datetime = dateadd(ss, cast(right(@start_time2, 2) as int), @start_datetime) end else begin set @start_datetime = dateadd(hh, cast(left(@start_time2, 2) as int), @start_datetime) set @start_datetime = dateadd(mi, cast(substring(@start_time2, 4, 2) as int), @start_datetime) set @start_datetime = dateadd(ss, cast(right(@start_time2, 2) as int), @start_datetime) end -- if PM, may need to convert to 24-hour clock if @start_time1 = 'PM' and cast(left(@start_time2, 2) as int) < 12 set @start_datetime = dateadd(hh, 12, @start_datetime) end -- test if in 00:00 format else if PATINDEX('[0-9][0-9]:[0-9][0-9]', @start_time2) = 1 and @start_time2 <> '00:00' begin -- if 12AM, only add minutes if (left(@start_time2,2)) = '12' and @start_time1 = 'AM' set @start_datetime = dateadd(mi, cast(substring(@start_time2, 4, 2) as int), @start_datetime) else begin set @start_datetime = dateadd(hh, cast(left(@start_time2, 2) as int), @start_datetime) set @start_datetime = dateadd(mi, cast(right(@start_time2, 2) as int), @start_datetime) end -- if PM, may need to convert to 24-hour clock if @start_time1 = 'PM' and cast(left(@start_time2,2) as int) < 12 set @start_datetime = dateadd(hh, 12, @start_datetime) end -- test if in 0:00 format else if PATINDEX('[0-9]:[0-9][0-9]', @start_time2) = 1 and @start_time2 <> '0:00' begin set @start_datetime = dateadd(hh, cast(left(@start_time2, 1) as int), @start_datetime) set @start_datetime = dateadd(mi, cast(substring(@start_time2, 3, 2) as int), @start_datetime) -- if PM, convert to 24-hour clock if @start_time1 = 'PM' set @start_datetime = dateadd(hh, 12, @start_datetime) end -- otherwise there is no valid timestamp: per DCE, assume 7:30 AM else begin set @start_datetime = dateadd(hh, 7, @start_datetime) set @start_datetime = dateadd(mi, 30, @start_datetime) end end end --********************************************************************************************** -- Compute the Ending DateTime --********************************************************************************************** -- Start by using the Completion Date and Completion Time fields select @end_datetime = g6_compl_dd, @end_time1 = g6_compl_t1, @end_time2 = g6_compl_t2 from [ACCELA].dbo.G6ACTION where REC_STATUS = 'A' and SERV_PROV_CODE = @serv_prov_code and B1_PER_ID1 = @b1_per_id1 and B1_PER_ID2 = @b1_per_id2 and B1_PER_ID3 = @b1_per_id3 and G6_ACT_NUM = @g6_act_num -- Determine the appropriate timestamp to use -- (skip this section if there is no EndDate) if @end_datetime is not null begin if datepart(hh, @end_datetime) = 0 and datepart(mi, @end_datetime) = 0 and datepart(ss, @end_datetime) = 0 begin -- extract h/m/s from @end_time2, based on the pattern of the string -- test if in 00:00:00 format if PATINDEX('[0-9][0-9]:[0-9][0-9]:[0-9][0-9]', @end_time2) = 1 and @end_time2 <> '00:00:00' begin -- if 12AM, only add minutes and seconds if left(@end_time2,2) = '12' and @end_time1 = 'AM' begin --set @end_datetime = dateadd(hh, cast(left(@end_time2, 2) as int), @end_datetime) set @end_datetime = dateadd(mi, cast(substring(@end_time2, 4, 2) as int), @end_datetime) set @end_datetime = dateadd(ss, cast(right(@end_time2, 2) as int), @end_datetime) end else begin set @end_datetime = dateadd(hh, cast(left(@end_time2, 2) as int), @end_datetime) set @end_datetime = dateadd(mi, cast(substring(@end_time2, 4, 2) as int), @end_datetime) set @end_datetime = dateadd(ss, cast(right(@end_time2, 2) as int), @end_datetime) end -- if PM, may need to convert to 24-hour clock if @end_time1 = 'PM' and cast(left(@end_time2, 2) as int) < 12 set @end_datetime = dateadd(hh, 12, @end_datetime) end -- test if in 00:00 format else if PATINDEX('[0-9][0-9]:[0-9][0-9]', @end_time2) = 1 and @end_time2 <> '00:00' begin -- if 12AM, only add minutes if (left(@end_time2,2)) = '12' and @end_time1 = 'AM' set @end_datetime = dateadd(mi, cast(substring(@end_time2, 4, 2) as int), @end_datetime) else begin set @end_datetime = dateadd(hh, cast(left(@end_time2, 2) as int), @end_datetime) set @end_datetime = dateadd(mi, cast(right(@end_time2, 2) as int), @end_datetime) end -- if PM, may need to convert to 24-hour clock if @end_time1 = 'PM' and cast(left(@end_time2,2) as int) < 12 set @end_datetime = dateadd(hh, 12, @end_datetime) end -- test if in 0:00 format else if PATINDEX('[0-9]:[0-9][0-9]', @end_time2) = 1 and @end_time2 <> '0:00' begin set @end_datetime = dateadd(hh, cast(left(@end_time2, 1) as int), @end_datetime) set @end_datetime = dateadd(mi, cast(substring(@end_time2, 3, 2) as int), @end_datetime) -- if PM, convert to 24-hour clock if @end_time1 = 'PM' set @end_datetime = dateadd(hh, 12, @end_datetime) end -- otherwise there is no valid timestamp: per DCE, assume 4:30 PM else begin set @end_datetime = dateadd(hh, 16, @end_datetime) set @end_datetime = dateadd(mi, 30, @end_datetime) end end end --********************************************************************************************** -- Compute the Duration; remove Weekend/Holiday Time --********************************************************************************************** set @duration = datediff(ss,@start_datetime, @end_datetime) if @duration < 0 set @duration = @duration + dbo.FN_GET_BLACKOUT_SECONDS(@start_datetime, @end_datetime) else set @duration = @duration - dbo.FN_GET_BLACKOUT_SECONDS(@start_datetime, @end_datetime) --********************************************************************************************** -- Return the result of the function --********************************************************************************************** RETURN @duration END and then in the CONTROL FLOW i have an EXECUTE SQL JOB: update dbo.FACT_InspectionDetails set SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS( ScheduledDateTime ,RequestedDateTime ,InspectionDateTime ) and when i execute the task, i'm getting the following error as insufficent arguments, please need help as where am i doing wrong? [Execute SQL Task] Error: Executing the query "update dbo.FACT_InspectionDetails set SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS( ScheduledDateTime ,RequestedDateTime ,InspectionDateTime )" failed with the following error: "An insufficient number of arguments were supplied for the procedure or function dbo.FN_GET_INSPECTION_DURATION_SECONDS.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. thanks

My suspicion materialized, you do use the DateDiff: set @duration = datediff(ss,@start_datetime, @end_datetime) Now please read my article above. I think this functioned needed to be unit testedArthur My Blog

Thanks Arthur, I still didnt get after reading the article , the scheduled job is set through the sql server and it is set for 2am and this job never failed until last week. Now when i set the job should i change the clock settings on my PC or on the PROD DERVER BOX?

You should not touch the server machine. We now want to simulate the failure to know better where the issue is. It is due to input. I assumed you tested your package locally with exactly the same data that was used in prod, therefore suggested to simulate it running at the time it failed. I am now at an equal impression it is the date values in the input that fail it. What do you say?Arthur My Blog

Please understand that I do not know your input range I took two values from above and got this error: SELECT DATEDIFF(SECOND,'2023-04-23 00:00:00.000' , '8201-06-30 00:00:00.000') caused Msg 535, Level 16, State 0, Line 3 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart. Arthur My Blog

Arhtur i excluded those records and the erro i'm getting now is: Execute SQL Task] Error: Executing the query "update dbo.FACT_InspectionDetails set SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS( ScheduledDateTime ,RequestedDateTime ,InspectionDateTime )" failed with the following error: "An insufficient number of arguments were supplied for the procedure or function dbo.FN_GET_INSPECTION_DURATION_SECONDS.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. It has soemthing to do with arguments in the execute sql task: update dbo.FACT_InspectionDetails set SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS( ScheduledDateTime ,RequestedDateTime ,InspectionDateTime )

You have it defined as @serv_prov_code varchar(15), @b1_per_id1 varchar(5), @b1_per_id2 varchar(5), @b1_per_id3 varchar(5), @g6_act_num but you did not supply @b1_per_id3 and @g6_act_num Arthur My Blog

Sorry about asking such basic questions. You said i defined @serv_prov_code varchar(15), @b1_per_id1 varchar(5), @b1_per_id2 varchar(5), @b1_per_id3 varchar(5), @g6_act_num but didnt supplu b1_per_id3 and g6_act_num, can you please tell me where i supplied for the rest and where shoud i supply for in the code? Thanks

You gave me the function definition as ALTER FUNCTION [dbo].[FN_GET_INSPECTION_DURATION_SECONDS] ( -- Add the parameters for the function here @serv_prov_code varchar(15), @b1_per_id1 varchar(5), @b1_per_id2 varchar(5), @b1_per_id3 varchar(5), @g6_act_num bigint ) But you call it in the package supplying fewer input parameters: update dbo.FACT_InspectionDetails set SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS( ScheduledDateTime ,RequestedDateTime ,InspectionDateTime )Arthur My Blog

so this update dbo.FACT_InspectionDetails set SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS( ScheduledDateTime ,RequestedDateTime ,InspectionDateTime ) should be replaced with update dbo.FACT_InspectionDetails set SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS( ScheduledDateTime ,RequestedDateTime ,InspectionDateTime,serv_prov_code varchar(15), b1_per_id1 varchar(5), b1_per_id2 varchar(5), b1_per_id3 varchar(5), g6_act_num )? Am i correct?

Not quite, thing is you need pass the proper values, not declarations, you did some mapping already in the package to pass the existing three, right? You will need to see how the (presumably) Execute SQL Task is configured. But I am frankly taken by surprise the package ever worked with missing parameters, are you sure you provided the proper function code?Arthur My Blog

yes thats the actual code USE [] GO /****** Object: UserDefinedFunction [dbo].[FN_GET_INSPECTION_DURATION_SECONDS] Script Date: 08/01/2012 16:07:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- =================================================================================== -- Author: -- Create date: 3/3/2010 -- Description: Given a unique inspection number, this function returns an integer -- representing the Inspection's duration in seconds. Follows logic -- originally incorporated into the "48-Hour Report" in Crystal. -- =================================================================================== -- ALTER FUNCTION [dbo].[FN_GET_INSPECTION_DURATION_SECONDS] ( -- Add the parameters for the function here @serv_prov_code varchar(15), @b1_per_id1 varchar(5), @b1_per_id2 varchar(5), @b1_per_id3 varchar(5), @g6_act_num bigint ) RETURNS int AS BEGIN --********************************************************************************************** -- Declare the return variable --********************************************************************************************** DECLARE @duration bigint --********************************************************************************************** -- Declare all other local variables --********************************************************************************************** DECLARE @start_datetime datetime DECLARE @start_time1 varchar(10) DECLARE @start_time2 varchar(10) DECLARE @end_datetime datetime DECLARE @end_time1 varchar(10) DECLARE @end_time2 varchar(10) --********************************************************************************************** -- Compute the Starting DateTime --********************************************************************************************** -- Start by using the SCHEDULED DATE select @start_datetime = g6_act_dd, @start_time1 = ltrim(rtrim(g6_act_t1)), @start_time2 = ltrim(rtrim(g6_act_t2)) from [ACCELA].dbo.G6ACTION where REC_STATUS = 'A' and SERV_PROV_CODE = @serv_prov_code and B1_PER_ID1 = @b1_per_id1 and B1_PER_ID2 = @b1_per_id2 and B1_PER_ID3 = @b1_per_id3 and G6_ACT_NUM = @g6_act_num -- Use REQUESTED DATE if not scheduled if @start_datetime is null begin select @start_datetime = g6_rec_dd, @start_time1 = ltrim(rtrim(g6_rec_t1)), @start_time2 = ltrim(rtrim(g6_rec_t2)) from [ACCELA].dbo.G6ACTION where REC_STATUS = 'A' and SERV_PROV_CODE = @serv_prov_code and B1_PER_ID1 = @b1_per_id1 and B1_PER_ID2 = @b1_per_id2 and B1_PER_ID3 = @b1_per_id3 and G6_ACT_NUM = @g6_act_num end -- Determine the appropriate timestamp to use -- (skip this section if there is no StartDate) if @start_datetime is not null begin if datepart(hh,@start_datetime)= 0 and datepart(mi,@start_datetime)= 0 and datepart(ss,@start_datetime)= 0 begin -- extract h/m/s from @start_time2, based on the pattern of the string -- test if in 00:00:00 format if PATINDEX('[0-9][0-9]:[0-9][0-9]:[0-9][0-9]', @start_time2) = 1 and @start_time2 <> '00:00:00' begin -- if 12AM, only add minutes and seconds if left(@start_time2,2) = '12' and @start_time1 = 'AM' begin --set @start_datetime = dateadd(hh, cast(left(@start_time2, 2) as int), @start_datetime) set @start_datetime = dateadd(mi, cast(substring(@start_time2, 4, 2) as int), @start_datetime) set @start_datetime = dateadd(ss, cast(right(@start_time2, 2) as int), @start_datetime) end else begin set @start_datetime = dateadd(hh, cast(left(@start_time2, 2) as int), @start_datetime) set @start_datetime = dateadd(mi, cast(substring(@start_time2, 4, 2) as int), @start_datetime) set @start_datetime = dateadd(ss, cast(right(@start_time2, 2) as int), @start_datetime) end -- if PM, may need to convert to 24-hour clock if @start_time1 = 'PM' and cast(left(@start_time2, 2) as int) < 12 set @start_datetime = dateadd(hh, 12, @start_datetime) end -- test if in 00:00 format else if PATINDEX('[0-9][0-9]:[0-9][0-9]', @start_time2) = 1 and @start_time2 <> '00:00' begin -- if 12AM, only add minutes if (left(@start_time2,2)) = '12' and @start_time1 = 'AM' set @start_datetime = dateadd(mi, cast(substring(@start_time2, 4, 2) as int), @start_datetime) else begin set @start_datetime = dateadd(hh, cast(left(@start_time2, 2) as int), @start_datetime) set @start_datetime = dateadd(mi, cast(right(@start_time2, 2) as int), @start_datetime) end -- if PM, may need to convert to 24-hour clock if @start_time1 = 'PM' and cast(left(@start_time2,2) as int) < 12 set @start_datetime = dateadd(hh, 12, @start_datetime) end -- test if in 0:00 format else if PATINDEX('[0-9]:[0-9][0-9]', @start_time2) = 1 and @start_time2 <> '0:00' begin set @start_datetime = dateadd(hh, cast(left(@start_time2, 1) as int), @start_datetime) set @start_datetime = dateadd(mi, cast(substring(@start_time2, 3, 2) as int), @start_datetime) -- if PM, convert to 24-hour clock if @start_time1 = 'PM' set @start_datetime = dateadd(hh, 12, @start_datetime) end -- otherwise there is no valid timestamp: per DCE, assume 7:30 AM else begin set @start_datetime = dateadd(hh, 7, @start_datetime) set @start_datetime = dateadd(mi, 30, @start_datetime) end end end --********************************************************************************************** -- Compute the Ending DateTime --********************************************************************************************** -- Start by using the Completion Date and Completion Time fields select @end_datetime = g6_compl_dd, @end_time1 = g6_compl_t1, @end_time2 = g6_compl_t2 from [ACCELA].dbo.G6ACTION where REC_STATUS = 'A' and SERV_PROV_CODE = @serv_prov_code and B1_PER_ID1 = @b1_per_id1 and B1_PER_ID2 = @b1_per_id2 and B1_PER_ID3 = @b1_per_id3 and G6_ACT_NUM = @g6_act_num -- Determine the appropriate timestamp to use -- (skip this section if there is no EndDate) if @end_datetime is not null begin if datepart(hh, @end_datetime) = 0 and datepart(mi, @end_datetime) = 0 and datepart(ss, @end_datetime) = 0 begin -- extract h/m/s from @end_time2, based on the pattern of the string -- test if in 00:00:00 format if PATINDEX('[0-9][0-9]:[0-9][0-9]:[0-9][0-9]', @end_time2) = 1 and @end_time2 <> '00:00:00' begin -- if 12AM, only add minutes and seconds if left(@end_time2,2) = '12' and @end_time1 = 'AM' begin --set @end_datetime = dateadd(hh, cast(left(@end_time2, 2) as int), @end_datetime) set @end_datetime = dateadd(mi, cast(substring(@end_time2, 4, 2) as int), @end_datetime) set @end_datetime = dateadd(ss, cast(right(@end_time2, 2) as int), @end_datetime) end else begin set @end_datetime = dateadd(hh, cast(left(@end_time2, 2) as int), @end_datetime) set @end_datetime = dateadd(mi, cast(substring(@end_time2, 4, 2) as int), @end_datetime) set @end_datetime = dateadd(ss, cast(right(@end_time2, 2) as int), @end_datetime) end -- if PM, may need to convert to 24-hour clock if @end_time1 = 'PM' and cast(left(@end_time2, 2) as int) < 12 set @end_datetime = dateadd(hh, 12, @end_datetime) end -- test if in 00:00 format else if PATINDEX('[0-9][0-9]:[0-9][0-9]', @end_time2) = 1 and @end_time2 <> '00:00' begin -- if 12AM, only add minutes if (left(@end_time2,2)) = '12' and @end_time1 = 'AM' set @end_datetime = dateadd(mi, cast(substring(@end_time2, 4, 2) as int), @end_datetime) else begin set @end_datetime = dateadd(hh, cast(left(@end_time2, 2) as int), @end_datetime) set @end_datetime = dateadd(mi, cast(right(@end_time2, 2) as int), @end_datetime) end -- if PM, may need to convert to 24-hour clock if @end_time1 = 'PM' and cast(left(@end_time2,2) as int) < 12 set @end_datetime = dateadd(hh, 12, @end_datetime) end -- test if in 0:00 format else if PATINDEX('[0-9]:[0-9][0-9]', @end_time2) = 1 and @end_time2 <> '0:00' begin set @end_datetime = dateadd(hh, cast(left(@end_time2, 1) as int), @end_datetime) set @end_datetime = dateadd(mi, cast(substring(@end_time2, 3, 2) as int), @end_datetime) -- if PM, convert to 24-hour clock if @end_time1 = 'PM' set @end_datetime = dateadd(hh, 12, @end_datetime) end -- otherwise there is no valid timestamp: per DCE, assume 4:30 PM else begin set @end_datetime = dateadd(hh, 16, @end_datetime) set @end_datetime = dateadd(mi, 30, @end_datetime) end end end --********************************************************************************************** -- Compute the Duration; remove Weekend/Holiday Time --********************************************************************************************** set @duration = DATEDIFF(ss,@start_datetime, @end_datetime ) if @duration < 0 set @duration = @duration + dbo.FN_GET_BLACKOUT_SECONDS(@start_datetime, @end_datetime) else set @duration = @duration - dbo.FN_GET_BLACKOUT_SECONDS(@start_datetime, @end_datetime) --********************************************************************************************** -- Return the result of the function --********************************************************************************************** RETURN @duration END GO based on the above function my update sql task was working without any issues until last week and now i'm very confused as whats going wrong with it