| Author |
Topic |
|
Tigger
Yak Posting Veteran
85 Posts |
 Posted - 2002-01-16 : 22:53:24
|
| I'm importing 800,000 customer records from Oracle into SQL Server but because the quality of data is suspect I need to set to null all dates of birth before 1900 eg 11/11/1111 is used when dob is unknown + there are other duff dates such as 1/01/722 !!I'm using the following ActiveX script (using VBScript) to clean the date If Not(IsNull(DTSSource("DOB_1"))) Then If Year(DTSSource("DOB_1")) < 1900 Then DTSDestination("Conv_DOB_1") = Null Else DTSDestination("Conv_DOB_1") = DTSSource("DOB_1") End IfElse DTSDestination("Conv_DOB_1") = NullEnd IfWhen I run it the DTS script fails saying "ActiveX Scripting Transform 'Transformations' encountered an invalid data value for DOB_1 source column"How can I find out what the invalid data value is? Is it possible to add error trapping to ActiveX so when it hits the invalid record I get a msgbox telling me what the value is?Help !! |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-01-16 : 23:04:24
|
Wow, a real live kiwi - we never see them over here is Aus! Now - regadrs your problem... I believe SQL server only recognises dates as far back as around 1792. I don't think Year() will work unless the value is recognised as a valid date. (and hence I think the Year() function won't work for 11/11/1111).If I were you, I'd be importing the date column into a nvarchar (ie string) field. Once you've got it into SQL Server, I'd then do your "alterations" by string manipulation.Other's may know better....I hope that when I die someone will say of me "That guy sure owed me a lot of money"Edited by - rrb on 01/16/2002 23:05:39 |
 |
|
|
Tigger
Yak Posting Veteran
85 Posts |
Posted - 2002-01-16 : 23:29:40
|
| Ah yes - good old string manipulation ... I had a feeling it might come down to that.BTW I'm actually a fake kiwi - I was a whinging pom but decided that kiwi's were cuter ... |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-01-16 : 23:53:54
|
| yep generally I always go nvarchar when coming from a source other than SQL Server.I forgot to mention why - . I've noticed that the Oracle Driver sometimes means that the DTS "suggested" data type is sometimes wrong.I had a great example when I was transferring the company General Ledger across from Oracle Financials. DTS was great because I just dumped in my Oracle Data source, drew my lines and away I went. The problem was that I found out later that some of the numbers had been "rounded" - . I didn't notice initially because of the hundred or so columns in the Oracle table - I was lazy and didnt' check each one.In short, my suggestion is, review all the datatypes that DTS "defaults" to for these transformations, because they may not be what you expect.PS - It doesn't matter where you're from. If Steve Waugh keeps up this rotation method, soon you'll all beat us!I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-17 : 01:01:20
|
Common! Steve Waugh is a good Captaian , didnt Aussies lose in india with the Best team quote:
It doesn't matter where you're from. If Steve Waugh keeps up this rotation method, soon you'll all beat us!
----------------------------------"True love stories don't have endings." |
|
|
|
Tigger
Yak Posting Veteran
85 Posts |
Posted - 2002-01-17 : 14:32:27
|
| Just checked the columns as you suggested and guess what ... the customer number which isan integer has been transferred as floating point !! So, thanks for the tip, I'm now going to recheck everythingAs for the cricket, well I think the rotation method is a great idea - Go the Black Caps !!! |
|
|
|
Tigger
Yak Posting Veteran
85 Posts |
Posted - 2002-01-17 : 15:13:17
|
| Ok, this is starting to drive me nuts !I'm trying to read in the date of birth, convert to string and then manipulate the year to get rid of the duff records before adding the record to the SQL table. The code I am trying to use is :If Not(IsNull(DTSSource("DOB_1"))) Then dob = CStr(DTSSource("DOB_1"))[then some string manipulation stuff]End IfHowever, when I run it I get an error message saying "ActiveX Scripting Transform 'Transformations' encountered an invalid data value for DOB_1 source column"I've tried it without the CStr and without the string manipulation stuff and get the same error. It doesn't seem to want to deal with DOB_1.I suspect it is because of the duff dates such as 11/11/0110 and 01/01/0001 - but how can I exclude them if it won't let me read them in first...Is the only option to download them first into a varchar column and then run a separate query once downloaded to fix the dates? I was hoping I could do it one step. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-01-17 : 18:20:53
|
Firstly, Nazim, let's get a few things straight....After another heart-wrenching loss last night to the dreaded little-brother (ie Kiwi-land), I think at this point it would be fair to say that Aussie never wins in India! But then India never wins anwyhere else!  Secondly, and back to the question. Tigger - I don't know how much they pay you, but I'd go for a minimum development time solution that works, and make your boss happy. It sounds like you've definitely got something funny coming from Oracle, so if I were you, I'd do the "varchar then query" thing as the next step in the DTS package - if you're not sure - ask me. Once you have it in a table as an SQL varchar, then you should be able to see where the problem is.As for the rotation method - if we keep going at this rate - even England will be able to beat us soon! Edited by - rrb on 01/17/2002 18:24:17 |
|
|
|
Tigger
Yak Posting Veteran
85 Posts |
Posted - 2002-01-17 : 18:46:25
|
OK, I've fixed it. The date value which was causing the problem was01-01-0001. Why that is a problem I don't know, however, by using the TO_CHAR Oracle function in my select statement (TO_CHAR(DOB_1, 'dd-mm-yyyy') as Conv_DOB1)it now downloads OK and then I can easily set to Null all records where yyyy < 1900.So that's date of birth sorted, now I just have to clean up names and addresses - oh what joy !!  |
|
|
|
|
ActiveX error trapping