DTS: Using ActiveX Script replace 0 value with NULL in a datetime format field.

via DTS
Source = .csv     Field = datetime

Possible values in source file = yyyyMMdd (20071008) OR 00000000
Due to the 00000000 value a normal "Date Time String transformation" fails. As such I would like to use an active X script to replace the 00000000 value with a NULL value leaving the yyyyMMdd value if available. I am unsure of the syntax in an ActiveX script. I have been trying something like the following without success.
Any help would be appreciated.

Function Main()
If DTSDestination("REF_DT") = ("000000000") Then
DTSSource("Col014") = IsNull Else
FormatDateTime( DTSDestination("REF_DT") , yyyyMMdd ) = FormatDateTime( DTSSource("Col014") , yyyyMMdd )
End If
End Function


pwanveerAsked:
Who is Participating?
 
pwanveerConnect With a Mentor Author Commented:
Got it. The following works:
'--------------------------------------------------------------------------------------------------------
Function Main()
     DTSDestination("REF_DT") = GetDate(DTSSource("Col014"))
     Main = DTSTransformStat_OK
End Function
'--------------------------------------------------------------------------------------------------------
Function GetDate (Byval Dat)

Dat = DTSSource("Col014")
yyyy = Left (DTSSource("Col014"), 4)
mm  = Mid(DTSSource("Col014"), 5, 2)
dd   = Right(DTSSource("Col014"), 2)

If (Dat) > 0 Then
      GetDate = FormatDatetime(mm&"/"&dd&"/"&yyyy)
Else
   GetDate = Null
End If

End Function
'--------------------------------------------------------------------------------------------------------
0
 
dbbishopCommented:
You have your source and destination mixed up:

If DTSSource("Col014") = ("000000000") Then
    DTSDestination("REF_DT") = NULL
Else
    DTSDestination("REF_DT") = FormatDateTime( DTSSource("Col014") , yyyyMMdd )
End If
0
 
pwanveerAuthor Commented:
dbbishop, the function still fails. DTS doesnt provide much more info than that unfortunately. Is the else syntax correct for copying the text value into the datetime format for a SQL field?

Else
    DTSDestination("REF_DT") = FormatDateTime( DTSSource("Col014") , yyyyMMdd )

Thx for the quick response.

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pwanveerAuthor Commented:
I'm trying a slightly different tact based on article:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21898627.html?sfQueryTermInfo=1+datetim+dt+format+name

I'm getting the correct results (Nulls for 0's and source values where the value is > 0) However I need to pass the end result as a datetime format to the SQL table.

Current:
'--------------------------------------------------------------------------------------------------------
Function Main()
     DTSDestination("REF_DT") = GetDate(DTSSource("Col014"))
     Main = DTSTransformStat_OK
End Function
'--------------------------------------------------------------------------------------------------------
Function GetDate (Byval Dat)

Dat = DTSSource("Col014")

If (Dat) > 0 Then
      GetDate = DTSSource("Col014")
Else
   GetDate = Null
End If

End Function
'--------------------------------------------------------------------------------------------------------

I've tried without sucess:

'--------------------------------------------------------------------------------------------------------
Function Main()
     DTSDestination("REF_DT") = GetDate(DTSSource("Col014"))
     Main = DTSTransformStat_OK
End Function
'--------------------------------------------------------------------------------------------------------
Function GetDate (Byval Dat)

Dat = DTSSource("Col014")

If (Dat) > 0 Then
      GetDate = FormatDateTime(DTSSource("Col014"), yyyyMMdd)
Else
   GetDate = Null
End If

End Function
'--------------------------------------------------------------------------------------------------------
0
 
Anthony PerkinsCommented:
>>I'm trying a slightly different tact based on article:<<
That is the correct way to do it :)
0
 
modus_operandiCommented:
Closed, 125 points refunded.
modus_operandi
Community Support Moderator
0
 
modus_operandiCommented:
Closed, 125 points refunded.
modus_operandi
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.