SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Description: Difference of two datetime columns ca		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

thinkingeye
Starting Member

USA
7 Posts
Posted - 07/23/2012 :  10:42:53  Show Profile  Reply with Quote
Hi all my package was running good until last weekend where i'm 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:04 AM Error: 2012-07-23 02:47:49.55 Code: 0x00000000 Source: Get Duration Description: Difference of two datetime columns caused overflow at runtime. End Error Error: 2012-07-23 02:47:49.56 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:04 AM Finished: 2:55:44 AM Elapsed: 3. The step failed.

I have no idea why it failed all of a sudden as nothing was changed. I the looked at the FUNCTION which i think was causing the problem, its an EXECUTE SQL TASK:
update dbo.TABLE_NAMEset SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS( )
ScheduledDateTime ,RequestedDateTime ,InspectionDateTime)
Can anyone please tell me what going wrong here?

Thanks

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 07/23/2012 :  12:05:59  Show Profile  Reply with Quote
that issue is data. you've some date value coming in data which when passed to function and when you apply date function (probably datediff) its causing return value to overflow the allowable value limit.

Open package in BIDS and run it for current data and store the records on to a table created in sql db. then inside table using a select query check case where value is going beyond limit when passed to function and you should get spurious data which caused issue. correct it in source and then run package again.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

thinkingeye
Starting Member

USA
7 Posts
Posted - 07/23/2012 :  12:51:04  Show Profile  Reply with Quote
Thanks Viskh i have the resulting dataset as 

scheduledtime     requestedtime        inspectedtime
2012-07-23 07:30:00.000	2012-07-23 09:27:00.000	2012-07-23 09:27:26.533
2012-07-23 07:30:00.000	2012-07-23 09:27:00.000	2012-07-23 09:27:27.033
2012-07-23 07:30:00.000	2012-07-21 07:30:00.000	2012-07-21 17:28:26.733
2012-07-23 07:30:00.000	2012-07-23 09:27:00.000	2012-07-23 09:27:27.423
2012-07-23 07:30:00.000	2012-07-23 09:27:00.000	2012-07-23 09:27:29.873
2012-07-23 07:30:00.000	2012-07-23 09:27:00.000	2012-07-23 09:27:30.607
2012-07-23 07:30:00.000	2012-07-23 09:27:00.000	2012-07-23 09:27:31.010
2012-07-23 07:30:00.000	2012-07-23 09:27:00.000	2012-07-23 09:27:31.417
2012-07-23 07:30:00.000	2012-07-23 09:32:00.000	2012-07-23 11:09:10.627

do you thinki have to use datediff with a less precise datepart.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 07/23/2012 :  12:53:17  Show Profile  Reply with Quote
it depends on logic you're using inside datediff. what date intervals were you passing to datediff? also whats was datatype you used for variable to store the datediff returned value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

thinkingeye
Starting Member

USA
7 Posts
Posted - 07/23/2012 :  14:00:48  Show Profile  Reply with Quote
set @duration = datediff(ss,@start_datetime, @end_datetime)

and the reutn dataype is an Integer
Edited by - thinkingeye on 07/23/2012 14:01:16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 07/23/2012 :  15:08:04  Show Profile  Reply with Quote
nope..i'm sure somewhere you're having bad data. the above shown data will not cause an error if used with expression above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000