I am using an ODBC Datareader to connect to a proprietary DB (Epex) and I need help on column mappings.
The Epex database has a number of time columns which we successfully imported using SQL 2000 DTS by defining these as varchar(5).
When using SSIS (Service Pack 1) these fields are shown as 'eight-byte signed integer [DT_I8]'.
I have attempted to these columns to both [DT_WSTR] and [DT_STR]' without success.
I can change the External Column type to [DT_WSTR] but I am unable to alter the Output Coulmns, when doing so I get the following error message:
Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.
Does anyone know how to resolve this issue?
Regards
JLF
Try to put the Data Conversion transform after your source.
|||You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.
However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.
|||Mark Durley wrote:
You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.
However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.
Mark
Thanks for youi response, I have tried to map the DT_I8 to a 'bigint' but to no avail.
This is a sample of the output from the SSIS conversion:
id Converted / Real Time
486539355 342000000000 09:30
486539406 342000000000 09:30
469762220 342000000000 11:10
335544346 342000000000 13:20
452984873 342000000000 13:40
436207662 342000000000 14:00
486539294 342000000000 14:45
486539325 342000000000 15:30
268435517 342000000000 15:40
285212826 342000000000 16:00
469762134 342000000000 16:00
As you can see there is no correlation between the Real Time and the Converted Time.
Does anyone elese have any ideas how to get around this, otherwise it's back to good old DTS?
Regards
|||So the value in the DT_I8 is always 342000000000, regardless of what the input was?
If you put a data viewer on the path from the data reader src, what values are you seeing in the DT_I8 columns there?
|||Mark
Sorry for the misleading example, the values do vary as per this output:
452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50
268435471 0 10:00
369098817 0 10:00
16777233 360000000000 10:00
I will try to put in a data view and let you know the results.
Cheers
John
|||Mark
Data viewer gives the same results as per my last post.
There seems to be no correlation between the values in the data reader and the 'real' values.
John
|||The data viewer will show exactly the same thing.
I took the first value there (354000000000) and applied the following calculation to it
354000000000 * 100 / 1000000000 / 60 / 60 = 9.8333333
Multiplying by 100 gives you the number of nanoseconds in the timespan
Dividing by 1000000000 gives you the number of seconds in the timespan
Dividing by 60 gives you the number of minutes in the timespan
Dividing by 60 again gives you the number of hours in the timespan = 9.8333333
You will notice that 9.833333 hours = 9h50m (or 9:50 in your notation)
So what Mark says is true. You need to do a calculation in order to derive the value. I have given you the basis of what you need to do. Note that you will need the modulus operator ('%') at some point.
An interesting little problem but its not that difficult!
-Jamie
|||
Jamie
Thanks for the input, but if you look at the first 5 results you can see there are 5 unique values ALL referring to the same time (9:50).
452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50
I don't think I can get around this via a calculation, it looks as if I need to retain my old 2000 DTS package just to handle time fields.
Regards
John
Has anyone any other ideas on how we can resolve this issue?
It would be a shame to have to revert back to our old DTS as SSIS is such a better product.
John
|||Reposted to put back on the forum list.
Any ideas anyone?
Mark Durley wrote: