December 7, 2011 at 8:15 am
I'm at a loss how I can get a truncation error here.
Based on these two dates I'm running this
EventTargetDateEventPulledDate
4/6/10 15:104/7/10 8:34
4/6/10 14:574/7/10 10:09
4/6/10 14:574/7/10 10:41
4/6/10 9:094/7/10 13:23
4/6/10 15:554/7/10 15:38
4/7/10 15:484/8/10 9:03
4/8/10 15:454/9/10 9:20
4/8/10 13:514/9/10 15:09
I have a Derived Column task that calculates what we call DelayWH (Work Hours)
DATEDIFF("n",(DT_DBTIME)EventTargetDate,(DT_DBTIME)"19:00:00") + DATEDIFF("n",(DT_DBTIME)"07:00:00",(DT_DBTIME)EventPulledDate)
Of 11million records... about 50,000 are failing with truncation. Near as I can tell there's no way I'm exceeding the bounds of a DT_I4 data type with this calculation.
Any ideas?
December 7, 2011 at 6:23 pm
I've not tried anything to prove this, but it seems that if you 're converting a string to DT_DBTime, the string should be in the format of hh:mm:ss. If so, then the date parts of the strings could be what are messing up the conversion and you'll want to edit them down to an hh:mm:ss format.
See BOL entry on SSIS datatypes at http://msdn.microsoft.com/en-us/library/ms141036.aspx.
The section on converting between strings and date/time types has this:
When you want to convert from a string to a date or time data type, the string must use the string format that corresponds to the appropriate date or time data type. For example, to successfully convert some date strings to the DT_DBDATE data type, these date strings must be in the format, "yyyy-mm-dd".
December 7, 2011 at 7:00 pm
mtassin (12/7/2011)
Of 11million records... about 50,000 are failing with truncation. Near as I can tell there's no way I'm exceeding the bounds of a DT_I4 data type with this calculation.Any ideas?
A way to help find the culprits, not necessarily how to fix them.
Toss a redirection on truncation error into the object that's whining. Add yourself a recordset destination with object variable in SSIS, and then drop a data viewer between whining object and destination recordset after pointing the redirection to it.
At the least, you'll be able to see which records are your problem children without having to manually try to chew your way through to them.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 8, 2011 at 6:50 am
Evil Kraig F (12/7/2011)
mtassin (12/7/2011)
Of 11million records... about 50,000 are failing with truncation. Near as I can tell there's no way I'm exceeding the bounds of a DT_I4 data type with this calculation.Any ideas?
A way to help find the culprits, not necessarily how to fix them.
Toss a redirection on truncation error into the object that's whining. Add yourself a recordset destination with object variable in SSIS, and then drop a data viewer between whining object and destination recordset after pointing the redirection to it.
At the least, you'll be able to see which records are your problem children without having to manually try to chew your way through to them.
I did... that's where the dates and times from above come from. 🙂
December 8, 2011 at 6:52 am
john.arnott (12/7/2011)
I've not tried anything to prove this, but it seems that if you 're converting a string to DT_DBTime, the string should be in the format of hh:mm:ss. If so, then the date parts of the strings could be what are messing up the conversion and you'll want to edit them down to an hh:mm:ss format.See BOL entry on SSIS datatypes at http://msdn.microsoft.com/en-us/library/ms141036.aspx.
The section on converting between strings and date/time types has this:
When you want to convert from a string to a date or time data type, the string must use the string format that corresponds to the appropriate date or time data type. For example, to successfully convert some date strings to the DT_DBDATE data type, these date strings must be in the format, "yyyy-mm-dd".
The strings I'm converting are "19:00:00" and "07:00:00", pretty sure those are in hh:mm:ss format. 🙂
The other values being converted are Database Datetime fields DB_DBTIMESTAMP which converts freely to DB_DBTIME.
The reason the date/time values above look different was that I had to copy them and paste them into excel to get them to line up nicely in columns. The values listed above were copied from a Data Viewer window, to excel so I could clean off fields I didn't want to post that were irrelevant to the issue, and then post them and get them to come up mostly columnized. Sorry for any confusion.
FWIW, converting all the values in SQL and doing the calculations there doesn't throw an error.
December 9, 2011 at 12:51 pm
mtassin (12/8/2011)
I did... that's where the dates and times from above come from. 🙂
D'oh, *facepalm*. Sorry.
I'm wondering if this is a case of SSIS complaining about the incorrect thing. Have you tried removing this columns from the process and seeing what happens? I know, it's a stretch.
Otherwise, it's got to have something to do with the formatting of the originals and what if anything Excel did in the meanwhile. I'll admit I don't use DT_DBTIME much if ever, so it may be an inconsistency in there instead that I'm just not familiar with.
Sorry Mark, that's about all I've got until I can explore some SSIS 2k8 processes using this and seeing if I can recreate it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 9, 2011 at 12:55 pm
Evil Kraig F (12/9/2011)
mtassin (12/8/2011)
I did... that's where the dates and times from above come from. 🙂D'oh, *facepalm*. Sorry.
I'm wondering if this is a case of SSIS complaining about the incorrect thing. Have you tried removing this columns from the process and seeing what happens? I know, it's a stretch.
Otherwise, it's got to have something to do with the formatting of the originals and what if anything Excel did in the meanwhile. I'll admit I don't use DT_DBTIME much if ever, so it may be an inconsistency in there instead that I'm just not familiar with.
Sorry Mark, that's about all I've got until I can explore some SSIS 2k8 processes using this and seeing if I can recreate it.
I only used Excel to format the data into columns so I could paste it here. 🙂 Mostly because I needed an intermediate spot so I could remove the columns easily that had things like customer names, and other information I can't share. 🙂
SSIS uses SQL as a source and as a destination, the entire process is using SQL Datetime, Date, and Time values, no weird conversion to varchar or nvarchar and back.
December 9, 2011 at 1:30 pm
Mark,
Is the error message completely unambiguous? Or is it possible that some other column is what's getting truncated? "Truncation" makes it sound like a character type. I might have expected an error on an integer to be called "overflow" instead. Can you share the actual message?
December 9, 2011 at 1:45 pm
john.arnott (12/9/2011)
Mark,Is the error message completely unambiguous? Or is it possible that some other column is what's getting truncated? "Truncation" makes it sound like a character type. I might have expected an error on an integer to be called "overflow" instead. Can you share the actual message?
Sorry,
The SSIS process I was working on was a one-shot job to back load some measures into a process that normally does a differential load, the differential load works fine, but wouldn't go back and update old measures. I wound up rebuilding the one-shot project using SQL to do all the date diff calculations. The error was on truncation and there's only a single derived column in the derived column transformation. The error was a generic truncation error.
I would have expected an overflow error myself if anything, but since I was putting the values into a DT_I4 value and was calculating the datediff in minutes between a time value and either 7pm or 7am, I don't see how that's possible either.
I no longer have the project that threw the error. So I guess we can go on, I was more curious if anybody had an idea that might resolve this should I run into it in the future.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply