I'm having a bit of a problem getting a date parsed from a text file, if anyone has some time please take a look at this DTS code and let me know if you have any ideas. Thanks in advance!
'************************************************* *********************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************* ***********************
Function Main()
DTSDestination("TableName") = DTSSource("Col001")
DTSDestination("Code") = DTSSource("Col002")
DTSDestination("Status") = DTSSource("Col003")
DTSDestination("ISN") = DTSSource("Col004")
DTSDestination("Description") = DTSSource("Col005")
DTSDestination("LongDescription") = DTSSource("Col006")
DTSDestination("OwnerSystem") = DTSSource("Col007")
DTSDestination("CodeFieldAttribute") = DTSSource("Col008")
DTSDestination("CodeFieldLength") = DTSSource("Col009")
DTSDestination("CodeEditRules") = DTSSource("Col010")
DTSDestination("EffectiveDateMaint") = DTSSource("Col011")
DTSDestination("StartDate") = getdate(DTSSource("Col012"))
'DTSDestination("StartDateInv") = getdate(DTSSource("Col013"))
DTSDestination("EndDate") = getdate(DTSSource("Col014"))
' DTSDestination("EndDateInv") = getdate(DTSSource("Col015"))
DTSDestination("Hold") = DTSSource("Col016")
Main = DTSTransformStat_OK
End Function
Function getDate(aDate)
Dim year, month, day
If Trim(aDate) = "" Or IsNumeric(aDate) Then
getDate = Null
Exit Function
End If
year = Mid(aDate, 1, 4)
month = Mid(aDate, 5, 2)
day = Mid(aDate, 7, 2)
getDate = DateSerial(year, month, day)
End FunctionLooking at your code leads to a few questions:
What is the error that you are getting? What is the value within DTSSource("Col012") and DTSSource("Col014")? Are the date fields all coming back NULL?
If so, then is the original column data in the format 20060725?
Also, the DateSerial function takes in numeric parameters, not strings, so you might need to convert the year, month, and day.
Finally, the section:
If Trim(aDate) = "" Or IsNumeric(aDate) Then
getDate = Null
Exit Function
End If
would work better as
If IsNumeric(aDate) Then
getDate = Null
Exit Function
Elseif Trim(aDate) = "" Then
getDate = Null
Exit Function
End If
Since Trim on a Numeric will cause an error, so it is good to test the trim after confirming it is not numeric.|||a simple rearrangemet of text date data will do the trick. try the code below (this assumes your text data is in the form of yyyymmdd otherwise you need to change the code a bit)
DTSDestination("StartDate") = left(DTSSource("Col012"),4) & "-" & mid(DTSSource("Col012"),5,2) & "-" & right(DTSSource("Col012"),2)|||Thanks guys, both your posts were very helpful. The problem actually was in the text file that I was trying to import, someone had entered dates in a yy/mm/dd format rather then yyyy/mm/dd once that was fixed the function performed as it should. Thanks alot!|||A couple of questions:
1.
With the supplied code
"
year = Mid(aDate, 1, 4)
month = Mid(aDate, 5, 2)
day = Mid(aDate, 7, 2)
"
wouldn't the slashes get in the way? I would guess that the code would have to change to:
"
year = Mid(aDate, 1, 4)
month = Mid(aDate, 6, 2)
day = Mid(aDate, 9, 2)
"
to get past whatever /,-, or . that was delimiting the date?
2. How does one Charboil something? I mean, by being in water wouldn't it get overcooked but not charred? :)
