Transforming a date field during a DTS

Posted on 2006-05-26
Last Modified: 2013-11-30
I have a DTS package that imports data from a flat .txt file into a table. In the flat file, the date format is: yyyymmdd. The format of the field (Date of Birth) in the table is dd/mm/yyyy. How can I perform this transformation within the DTS package? Here is the transformation code directly from the DTS package:

Function Main()
      DTSDestination("mrTIMESTAMP") = DTSSource("Col001")
      DTSDestination("Last__bName") = DTSSource("Col002")
      DTSDestination("First__bName") = DTSSource("Col003")
      DTSDestination("Date__bof__bBirth") = DTSSource("Col004")
      DTSDestination("Phone") = DTSSource("Col005")
      DTSDestination("Medical__bRecord__b__3") = DTSSource("Col006")
      DTSDestination("Patient__bLocation") = DTSSource("Col007")
      Main = DTSTransformStat_OK
End Function
Question by:isaacr25
    LVL 8

    Expert Comment

    You can change the type of transformation for Date of Birth column
    from a default data transform task
    to a ActiveX script task.

    In the activex script task you can convert the date from yyyymmdd to dd/mm/yyyy.

    Follow these steps to do that:
    1. right click the package and choose 'design package'
    2. double click the transform data task [arrow] (the one which start from the datasrc to datadest)
    3. In the 'transform data task properties' dialog choose 'transformations' tab
    4. the tab will list all the transformations created by the system in default.  choose the transform arrow for dob and delete that.
    5. now create a new transformation by clicking [new] button
    6. choose activex script from the options in the 'create new transformation' dialog
    7. in the 'transformation options' dialog choose 'source column' and make sure the source dob column is selected in the list of selected columns, do the same for destination columns and go to the general tab in the same dialog and click properties.  That will show a default activex script.
    8. You can edit this script, to do the conversion of the source {DTSSource("dob")} and finally store it in {DTSDestination("dob")}
    9. press ok.
    10. this way you can change the normal transformations as you like.

    Note: but this might take time, based on the size of the file you are using.

    Author Comment

    I've followed you all the way to setp 8. Can you provide some more detail as to the code that I need to achieve this transformation? Here's the existing code:

    '  Copy each source column to the destination column
    Function Main()
          DTSDestination("Date__bof__bBirth") = DTSSource("Col004")
          Main = DTSTransformStat_OK
    End Function

    Also, you mentioned the timeliness of this process. Are there any other ways that could be less time-consuming? Thanks.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Assuming that Date__bof__bBirth is a datetime column than use change this:

    DTSDestination("Date__bof__bBirth") = DTSSource("Col004")

    DTSDestination("Date__bof__bBirth") = CDate(DTSSource("Col004"))

    Author Comment

    I get the following when I try running the package:

    Type mismatch: 'CDate"

    The DOB field in the table is a datetime field.
    LVL 75

    Accepted Solution

    You are absolutely right, sorry about that.  Try it this way:

    Function Main()
         DTSDestination("Date__bof__bBirth") = GetDate(DTSSource("Col004"))
         Main = DTSTransformStat_OK
    End Function

    ' Use a function to get better control and so you can reuse the code for other datetime columns
    Function GetDate(ByVal Value)

    ' First convert to a date friendly format (yyyy-mm-dd)
    Value = Left(Value, 4)  + "-" + Mid(Value, 5, 2) + "-" + Mid(Value, 7, 2)

    If IsDate(Value) Then                ' Is it a valid date?
       GetDate = CDate(Value)
       GetDate = Null                       ' If needed replace with some other appropriate value for invalid dates
    End If

    End Function

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now