I am using a DTS package to load a table from a text file. One of the transformations involves transforming a string (in the format of mm/dd/yyyy) into a datetime field in the same format (mm/dd/yyyy). Since the source field field is character and the destination field is datetime, I could not of course simply do a copy transformation. Neither would SQL Server allow me to do a simple datetime transformation. As a result, I have been experimenting with trying an Active X transformation using the CDate() function in an effort to convert the string to a date. The only problem is that I keep getting a Cdate type mismatch error, and I don't know how to resolve it. Currently this is how the Active X transformation looks:
Function Main() DTSDestination("Date")=CDate (DTSSource="Col011")) Main = DTSTransformStat_OK End Function
Could you please shed some light on why I keep getting the type mismatch error and what I might try to correct it?
You should be able to simply use a convert function to convert the data. If there is an error generated, it is because one or more of the rows contains an invalid date.