Results 1 to 5 of 5

Thread: DTS Problem

  1. #1
    Join Date
    Jul 2006
    Posts
    2

    Unanswered: DTS Problem

    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 Function
    Last edited by Charboil; 07-25-06 at 17:53.

  2. #2
    Join Date
    Jul 2006
    Posts
    87
    Looking 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.

  3. #3
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    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)

  4. #4
    Join Date
    Jul 2006
    Posts
    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!

  5. #5
    Join Date
    Jul 2006
    Posts
    87
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •