Hi,

Could someone help me with the Datetime DTS Transformation?
I try to import the Text (fixed length) file into the SQL table and having
problem when the date from the Text file is blank. I have two columns
(from text file) with the dates in format yyyyMMdd. One of them always
have valid dates, which go to the datetime column on the SQL table fine.
The other one, it gives me an error on the first row containing blank in
the Text file even though I allow null on that column. Is anyway I make it
put "Null" in table if the import text is blank?

Thanks very much in advance.

Todoan

--
Message posted via http://www.sqlmonster.com

Sure you can, do an ISNULL(ColumnName) check with the ActiveX script for
that transformation, or check that the date is blank ColumnName = ''.
If it is, set the destination to NULL, else set it to the value of the
record.

--
Simon Worth


"T Tran via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote

Hi,
Thanks for your reply.
I still get the error. Could you tell me what did I do wrong?

Here is my ActiveX script:

Function Main()
If DTSSource("Col031") = Space(8) Then
DTSDestination("TermDate") = Null
Else
DTSDestination("TermDate") = DTSSource("Col031")
End if
Main = DTSTransformStat_OK
End Function

All the rows containing the valid date (in format YYYYMMDD), the error is:

Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Error Description:ActiveX Scripting Transform 'DTSTransformation__17'
encountered an invalid data value for 'TermDate' destination column.
Error Help File:sqldts80.hlp
Error Help Context ID:30632
@@SourceRow: Logged
@@DestRow: Not Available

Thank you very much for your time.
--todoan

--
Message posted via http://www.sqlmonster.com

What is the datatype for TermDate?
You are trying to insert a record that doesn't have a compatible datatype
format for the destination column.

--
Simon Worth


"T Tran via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote

Thank you so much for all the replying.
I didn't know that the system expects the input date in the format
"MM/dd/yyyy".

I got it worked after changing the scripts to:

Function Main()
Dim tDate
Dim tDate2

If DTSSource("Col015") = Space(8) Then
DTSDestination("ReHireDate") = Null
Else
tDate = DTSSource("Col015")
tDate2 = mid(tDate,5,2) + "/" + right(tDate,2) + "/" + left(tDate,4)
DTSDestination("ReHireDate") = cDate(tDate2)
End if
Main = DTSTransformStat_OK
End Function

--
Message posted via http://www.sqlmonster.com