February 11, 2010 at 11:02 am
I'm trying to write an ssis package that is converting a sybase sql database table in to a sql server 2005 table. The only problem is it's not recognising sybase time data types very well. It looks at the field in my datareader data flow source and is recognising it as DT_I8, and I get the following error message:
Conversion from "DT_I8" to "DT_DBTIME" is not supported
How do I get this in to a datetime field in sql 2005? I'd assume it would work if I had sql 2008 as there's a time data type in that version of sql!
February 17, 2010 at 12:37 am
Griffster (2/11/2010)
Conversion from "DT_I8" to "DT_DBTIME" is not supported
Can you give an example of how SSIS sees the input data?
My first guess is to convert it with a Data Conversion to a string, then manipulate it with a Derived Column into a datetime format that SQL 2005 recognizes. Maybe you need another Data Conversion to convert the manipulated string to the datetime format.
More information about converting strings to dates in SSIS:
http://www.sqlservercentral.com/Forums/Topic609337-148-1.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 17, 2010 at 2:06 am
An example is that it sees the time in Sybase (Sybase 11) table as 12:52:39.000, but SSIS sees the value as a DT_I8 with a value of 463590000000.
February 17, 2010 at 2:48 am
Griffster (2/17/2010)
An example is that it sees the time in Sybase (Sybase 11) table as 12:52:39.000, but SSIS sees the value as a DT_I8 with a value of 463590000000.
Such a large number indicates that the times in Sybase are possibly stored as time_t values. In other words, the number of seconds after January 1, 1970. (for more information on time_t, see http://en.wikipedia.org/wiki/Time_t)
You could resolve this with a derived column that looks like this:
select dateadd(ss, 463590000000, 'jan 1 1970')
but unfortunately the number 463590000000 is too big for an int and the component fails.
You could write a script that splits the number into (a lot of) smaller numbers that fit into the datatype int and use a loop to add each one to 'jan 1 1970' with the dateadd function.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 19, 2010 at 10:51 am
Koen Verbeeck (2/17/2010)
Griffster (2/17/2010)
An example is that it sees the time in Sybase (Sybase 11) table as 12:52:39.000, but SSIS sees the value as a DT_I8 with a value of 463590000000.Such a large number indicates that the times in Sybase are possibly stored as time_t values. In other words, the number of seconds after January 1, 1970. (for more information on time_t, see http://en.wikipedia.org/wiki/Time_t)
You could resolve this with a derived column that looks like this:
select dateadd(ss, 463590000000, 'jan 1 1970')
but unfortunately the number 463590000000 is too big for an int and the component fails.
Ewh. How irritating is that?! Would have worked nicely if MS had not put such a restriction on there!
You could write a script that splits the number into (a lot of) smaller numbers that fit into the datatype int and use a loop to add each one to 'jan 1 1970' with the dateadd function.
This could get quite messy especially if I had 3 or 4 Date fields in the same table to convert. Are you saying to link from the data reader source to a script component within the data flow task? Do you know of an example I could follow on what you're suggesting here?
February 24, 2010 at 12:22 am
Forget about my previous post. I've tried to convert the number using the time_t specification, but it gives an overflow. Which actually makes sense. There are about 31536000 seconds in one year, which would mean that 463590000000 stands for 14700 years.
I've did some more research on the time data type in sybase, and normally the time is stored in 8 bytes (although I've found other sources that contradict that, saying it is stored in 4 bytes). 8 bytes makes sense though, it explains the big numbers like 463590000000 (= 00000000 00000000 00000000 01101011 11110000 00011110 00000101 10000000 in binary).
I've tried in SQL Server to load a binary variable with the value above and convert it to the datetime data type, but it always gives a conversion error. Maybe the problem lies in the fact that SQL Server doesn't really have a dedicated time data type?
Maybe you could try to convert the time values in Sybase itself to string values and then import them to SQL Server?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 24, 2010 at 9:09 am
Griffster (2/17/2010)
An example is that it sees the time in Sybase (Sybase 11) table as 12:52:39.000, but SSIS sees the value as a DT_I8 with a value of 463590000000.
Looks like the first part is time in seconds: ((12 * 60) + 52) *60) + 39 = 46359.
The maximum value you can get here is 86399 (23:59:59), so maybe the first 5 digits are the time in seconds and the remainder is the fractional part (.000) in your example. Use the advanced editor to change the metadata to I8, try to get the data in and then used derived columns to convert.
February 24, 2010 at 9:45 am
wschampheleer (2/24/2010)
Griffster (2/17/2010)
An example is that it sees the time in Sybase (Sybase 11) table as 12:52:39.000, but SSIS sees the value as a DT_I8 with a value of 463590000000.Looks like the first part is time in seconds: ((12 * 60) + 52) *60) + 39 = 46359.
The maximum value you can get here is 86399 (23:59:59), so maybe the first 5 digits are the time in seconds and the remainder is the fractional part (.000) in your example. Use the advanced editor to change the metadata to I8, try to get the data in and then used derived columns to convert.
Think you're right! Just checked another time to be sure. In sybase the time was 20:59:09.000. In I8 it's 755490000000. If I then apply your formula it will be:
((20 * 60) + 59) * 60) + 9 = 75549.
For the field in question in my data reader > input output properties > external columns, the datatype is already eight byte signed integer (DT_I8). Should I be converting it to a string data type first and then do a derived column transformation on this string looking at the leftmost 5 characters to get the time?
February 24, 2010 at 12:53 pm
wschampheleer (2/24/2010)
Looks like the first part is time in seconds: ((12 * 60) + 52) *60) + 39 = 46359.
Well, that's genious. Why didn't I think of that? :hehe:
Griffster (2/24/2010)
For the field in question in my data reader > input output properties > external columns, the datatype is already eight byte signed integer (DT_I8). Should I be converting it to a string data type first and then do a derived column transformation on this string looking at the leftmost 5 characters to get the time?
I think you should't change it in the metadata properties in the data reader. With SSIS, you'll never know how it will mess up metadata the next time 🙂
You can cast the DT_I8 in the Derived Column Transformation itself, take a look at the pre-defined casting functions. After you've converted it to string, you can use the left, right or substring function to get the parts that you need.
Well, I'm glad that you're moving towards a solution now 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 25, 2010 at 12:37 am
For the field in question in my data reader > input output properties > external columns, the datatype is already eight byte signed integer (DT_I8). Should I be converting it to a string data type first and then do a derived column transformation on this string looking at the leftmost 5 characters to get the time?
That is certainly an option, but sicne you'll have to do some arithmetic to calculate the hours, minutes and seconds anyway, I would leave it as an integer and simply divide by 10000000 as you go.
Personally however, I'm not a big fan of the derived column transformation (has too many shortcomings) so what I would do (if possible) is do the calculations on the sybase machine in sql and return a string in the format hh:mm:ss. You can cast this then to a date time format in SSIS.
February 25, 2010 at 4:50 am
wschampheleer (2/25/2010)
For the field in question in my data reader > input output properties > external columns, the datatype is already eight byte signed integer (DT_I8). Should I be converting it to a string data type first and then do a derived column transformation on this string looking at the leftmost 5 characters to get the time?
That is certainly an option, but sicne you'll have to do some arithmetic to calculate the hours, minutes and seconds anyway, I would leave it as an integer and simply divide by 10000000 as you go.
Personally however, I'm not a big fan of the derived column transformation (has too many shortcomings) so what I would do (if possible) is do the calculations on the sybase machine in sql and return a string in the format hh:mm:ss. You can cast this then to a date time format in SSIS.
That may be easier said than done really. Will give the derived column transformation for now.
February 25, 2010 at 9:57 am
How I hate SSIS error reporting! I don't get any errors now in the design, but I'm redirecting errors in the insert in to my Sql tables, and they're all being redirected. In my error file the error code and column on all of them is:
-1071607689 and 512
respectively.
I can work out what the error code refers to:
The data value cannot be converted for reasons other than sign mismatch or data overflow.
The problem is though, working out which column is the problem. I don't know how to find out column 512! Is there a better way rather than clicking ignore on each field one at a time in the ole db destination task?
February 25, 2010 at 10:34 am
February 26, 2010 at 4:38 am
DATEADD("ss",TIME / 10000000,(DT_DBTIMESTAMP)"01 Jan 1900")
where TIME is the DT-I8 field I'm converting and the destination is a database timestamp field.
February 26, 2010 at 5:11 am
Griffster (2/26/2010)
DATEADD("ss",TIME / 10000000,(DT_DBTIMESTAMP)"01 Jan 1900")where TIME is the DT-I8 field I'm converting and the destination is a database timestamp field.
So basically a combination of wschampheleer's solution and my solution 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply