December 19, 2008 at 9:33 am
Hello,
I have an ActiveX script that checks the incoming field for numeric (will expand later to check other possibilities) if not I am attempting to send NULL. The step fails when inserting this value.
Function Main()
' First want to check incoming source for non numeric values
Dim iDay
Dim iMonth
Dim iYear
Dim AllFields
iDay = Mid(DTSSource("Col020") , 7 ,2)
iMonth = Mid(DTSSource("Col020") , 5 ,2)
iYear = Mid(DTSSource("Col020") ,1 ,4)
AllFields = iYear&"/"&iMonth&"/"&iDay
If IsNumeric(DTSSource("Col020")) Then
DTSDestination("paymentdate") = AllFields
Else
DTSDestination("paymentdate") = NULL
End If
Main = DTSTransformStat_OK
End Function
Incoming Field Data '20080103' --- Valid
' ' --- InValid
The paymentdate column is SmallDateTime (Allows NULLS)
Any ideas???
Thanks
Bill
December 19, 2008 at 12:17 pm
OK, it wasn't the NULL insert. It was incoming dates of 00000000. I found these by opening the Transform Data Task Properties, going to the 'Options' tab and creating an Exception file. Really handy item. Set the failure threshold low enough to kill the process, yet give me a pretty good look at the errors, and found the zeros in the field. The incoming file has over 2 million rows, so scrolling through was a pain.
Any way altered my code to read:
Function Main()
' First want to check incoming source for non numeric values
Dim iDay
Dim iMonth
Dim iYear
Dim HoldingVar
Dim AllFields
'Put the DTS colum in a var
HoldingVar = DTSSource("Col020")
'Set to nothing if all zeros
If HoldingVar = "00000000" Then
HoldingVar = ""
End If
' Split apart for proper date
iDay = Mid(DTSSource("Col020") , 7 ,2)
iMonth = Mid(DTSSource("Col020") , 5 ,2)
iYear = Mid(DTSSource("Col020") ,1 ,4)
' Put back together nicely
AllFields = iYear&"/"&iMonth&"/"&iDay
'Check fer numericy.....
If IsNumeric(HoldingVar) Then
DTSDestination("sedp-payment-paid-date") = AllFields
Else
DTSDestination("sedp-payment-paid-date") = NULL
End If
Main = DTSTransformStat_OK
End Function
CYA 😀
December 22, 2008 at 8:55 am
Even better, get rid of your HoldingVar code and do this:
If IsDate(AllFields) Then
DTSDestination("sedp-payment-paid-date") = AllFields
Else
DTSDestination("sedp-payment-paid-date") = NULL
End If
December 22, 2008 at 3:08 pm
Much cleaner, thanks!:w00t:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply