June 10, 2003 at 4:58 am
I am writing a DTS package to import data from a FoxPro database into an SQL server database.
I am using multiphase data pump on pulling the data in as I have some dodgy dates to import (e.g year is set to 200 instead of 2000, or the year is set to 1053 etc). On the whole it is working ok, except there is one date with the year set to 0031. When I try to run the DTS with this date included, unless I have On Transfer Failure phase set to 'DTSTransformStat_SkipRow' with no other code in it, the whole DTS package falls over with the error - ActiveX Scripting Transform '<DTS Testing Transformation>' encountered an invalid data value for 'dob' source column. Error Help File:sqldts80.hlp Error Help Context ID:30632
The problem is that I want to transform the other dates, not skip all of the invalid rows, and when i try to add additional code into On Transfer Failure phase, it won't work. I presume the reason it is failing is that VBscript dates start from 100AD.. I know I can change the date in the original date to fix it but I was wondering why this problem was causing the whole dts package to fall over, and not seeming to run through the multiphase DTS code.
This data is still being added to, while we develop this database, so I can't guarantee someone won't add another dodgy date, and i would like to get this dts package running ok without having to keep manually altering dates.
Thanks!
June 10, 2003 at 7:42 am
I have the same problem with DTS from a 3rd party db using their odbc driver. My dates are typically 0203-06-10 format (typo!!).
What I do is to change the transformation for the column from copy to activex and test the date like this
If Year( DTSSource("Date") ) < 1900 Then
DTSDestination("Date") = null
Else
DTSDestination("Date") = DTSSource("Date")
End If
Main = DTSTransformStat_OK
which will produce a null date if the year is less than 1900.
Far away is close at hand in the images of elsewhere.
Anon.
June 10, 2003 at 10:23 pm
WOuld something like this
Case When isdate(DateCol) = 1 then DateCol else Null end
work in a DTS extract?
June 11, 2003 at 2:26 am
rflewitt,
The problem with your statement is that sql still has to convert the date to do the isdate check and would fail.
Far away is close at hand in the images of elsewhere.
Anon.
June 11, 2003 at 3:50 pm
Sorry - I don't wish to side track the problem with my ignorance but ... 😉
How about if the destination column for the import is a varchar or char column and you run the test on it. Then you can create additional sql (maybe as an aditional step in the dts package to move the destination table to one with a correct datetime column).
- Richard
June 12, 2003 at 2:32 am
Good one Richard. However in my case if the year is less than 1900 (suspect date) then I don't want the date anyway (what would be the correct year?) that is why a set it to null. Still not perfect but it works for me.
However I like your suggestion and will bear it in mind for the future.
Far away is close at hand in the images of elsewhere.
Anon.
June 12, 2003 at 2:45 am
The problem is that when i test IsDate on it (in the access module visual basic test - i have no other way of testing!), it converts the date of 0031, to 1931 or 2031 - so it passes the isDate test... That year is the only one I have a problem with, everything else works fine. Seems to me that the DTS package can't cope with the year and just junks it everytime i run it. I am resigned to having to manually change that date before importing the data properly. It doesn't matter as it is only 1 date, but I would like to know why it is not stepping through the code as it should.
June 12, 2003 at 3:01 am
IsDate in MS VBA does allow strange dates, I belive it only does generalized checking and in your case would always work.
SQL seems to have trouble converting dates, try this
declare @date datetime
set @date = '0031-01-01'
select @date
and you will get an error. However if you use '01-01-31'or '01-01-0031' you will get 2031-01-01. There is an option for SQL server for Year 2000 support and interpreting two digit years (not in your case as you have 4 digit year).
If you have a year of 0031, what is it supposed to be? If you know then you could do as I suggested earlier and use ActiveX script in the transformation.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply