Transforming a date field during a DTS

Posted on 2006-05-26
Medium Priority
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
  • 2
  • 2

Expert Comment

ID: 16769793
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

ID: 16769856
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
ID: 16770718
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

ID: 16770748
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

Anthony Perkins earned 2000 total points
ID: 16773870
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

862 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