none
Date Difference Out of Range error message

    Question

  • Hi All,

    I'm loading  data from a application level table (source db) using a package and after the load is over i'm using the "execute sql task editor " to update a column in the DESTINATION TABLE.

    update dbo.FACT_Details
    set SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS(
    	ScheduledDateTime
    	,RequestedDateTime
    	,InspectionDateTime
    )

    But during the execution i'm getting the error message tha the DATEDIFF function is out of range, which makes sense , since i'm getting the datediff in SS and i have a date with YEAR=3013 instead of 2013 which was a typo mistake on the application side. What i'm looking for " is there a way where in when the package encounters this kind of dtae where it is out of range  it just skips the DATEDIFF Function part" but loads the rest of the data. Please advice.

    Thanks

    Monday, November 18, 2013 10:41 PM

Answers

All replies

  • I would suggest testing the data with a conditional split component before sending it to the destination.  This will let you redirect the row to a different file/destination for inspection.  

    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

    Monday, November 18, 2013 10:47 PM
    Moderator
  • Thanks Phil. I have that conditional split in place where it will send the records with bad dates to a different table. But what i was looking for to bring the records with bad dates as well into the final destination table and if the dates are out of range just bypass the UPDATE execute sql task. Does my request make any sense?

    Tuesday, November 19, 2013 2:33 PM
  • But what i was looking for to bring the records with bad dates as well into the final destination table

    yes you can bring the bad rows into final destination table, only when YOU FIX THE BAD DATES. When the bad dates will be fixed, datediff will not error out.


    Thanks, hsbal

    Tuesday, November 19, 2013 3:33 PM
  • Thanks Phil and Harry Bal. I 'm thinking of something like this. If i add another task before the execute sql task which updates the datediff function. This new task will look for the years and check if they are within the range if YES then no UPDATE will be done else will UPDATE to CURRENT year. Will try executing this tomorrow and will give you guys feedback.

    Thanks a lot1

    Tuesday, November 19, 2013 9:40 PM