dts and bringing across/converting invalid dates 2

[swoodring]

I am using a dts to bring over data from our as/400. In the file one field is a date and sometimes it contains an invalid date i.e. 0001-01-01. How do I go about converting it in the dts. Most of the time I don't need to because its a valid date but every once in a awhile theres an invalid date. If I understand correctly sql server won't accept a date of 0001-01-01.

Here's something similiar to the select statment I'm using in my dts. What I would like to do is if the datefield is 0001-01-01 to default it to 1900-01-01 otherwise if its fine just bring it across.

Example
Select field1, field 2, datefield from ipledgr

 

[foxdev]

In your DTS job you could use VBScript for that particular transform; the script could check for an invalid date and default it if it was bad.

Another alternative is to import the data into a staging table where the date column is Char type, then use T-SQL to Update a separate date column as appropriate, then Insert into the final destination table.
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[swoodring]

I'n not familiar with VB script. I'm assumnig I woould do it in an active X script. Can you give me an example of the code I would use?

 

[foxdev]

I was afraid you were going to ask that. In the DTS Transformations page you can choose that you will be using ActiveX/VBScript rather than copying the column; when the script editing dialog appears, there will be a good selection of built-in functions that can be used. I've successfully slogged through it before and I know nothing about VBScript - which is why I'm avoiding your question.
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[cdukes]

How about the following which I have not tested:

'**********************************************************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************************************

Function Main()

'' If the source data is a valid date
'' then use it, otherwise default to
'' 01 jan 1900
if isDate(DTSSource("AS400Date&quot) = true then
DTSDestination("MyDate&quot = DTSSource("AS400Date&quot
else
DTSDestination("MyDate&quot = "01 jan 1900"
end if

Main = DTSTransformStat_OK

End Function


Hope this helps,

Chris Dukes