Let’s say you are importing data from a flat file to SQL Server via DTS. Now, let’s say the date format in the source file is similar to this:
YYYYMMDD.
If your destination column is a datetime or smalldatetime, SQL server isn’t going to know what to do with this data, at least not initially. So, you’re going to have to use ActiveX copy instead of the plain old Copy Column Task. Here is the code you would use in your ActiveX transformation within the Data Pump:
'**************************************
' Visual Basic Transformation Script
'**************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("myDate") = left(DTSSource("badDate"),4)+"-"&_
+mid(DTSSource("badDate"),5,2)+"-"+right(DTSSource("badDate"),2)
Main = DTSTransformStat_OK
End Function
Substitute myDate for your destination and badDate for the source. This will give you a nice result in the YYYY-MM-DD format.
If the source is a datetime stamp, you can expand this technique to parse time. You would have to use another Mid to pull the hours and minutes. Experiment and see what else you can find.
Remember, this is an ActiveX script within a Data Pump task.
