Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pwvailla
Starting Member
31 Posts |
 Posted - 2010-10-27 : 11:22:50
|
| Hello,I am trying to import a view from an Oracle instance into my SQL Server 2005 database. I have the linked server in place. The process starts but about row 9290 it blows up with: "Invalid character value overflowed the specified type" Error 0xc020901c: Data Flow TaskIt is a date field, LAST_RECEIVED_DATE.I know I have some crap data stuffed into this field but I don't know how to overcome this problem. It is a view comprised of 6 tables.1. Is there a way to transform the data as it is imported? I am using Standard Edition.2. Can I tackle this another way by writing native SQL to access an Oracle table across a linked server? What would that SELECT look like? e.g SELECT * FROM LINKEDSERVERNAME.TABLENAME/USERNAME/PASSWORD? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-10-27 : 13:15:08
|
| just check using isdate() function to identify spurious date values.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
pwvailla
Starting Member
31 Posts |
Posted - 2010-11-19 : 13:37:59
|
| How would I do so? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-11-21 : 03:52:03
|
| do like CASE WHEN ISDATE(LAST_RECEIVED_DATE)=1 THEN LAST_RECEIVED_DATE ELSE NULL END------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2010-11-21 : 13:08:33
|
If the source column is not a datetime column and may contain strings of non-dates...in SSIS Data flow you would have to send the source column through a conversion or derived column and send the results of the derived/transformed column to the destination. Thw wizard won't allow you to handle this directly. If the source column is a datetime column, you might see this error if trying import into a smalldatetime column and valid dates were out of range.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
|
|
Data Value Overflowed using IMPORT Utility