hu8mypho
asked on
DTS Import datetime field problem from Flat File SQL2000
I am trying to import large flat files into my Table using DTS.
The import always failed due to the Date fields error:
TransformCopy 'DirectCopyXForm' conversion error: Conversion invalid for datatypes on column pair 27 (source column 'Col027' (DBTYPE_STR), destination column 'last_modified' (DBTYPE_DBTIMESTAMP)).....
My current solution, is to convert those fields to varchar and then import the file, then reconvert them back to datetime. It works, but there has to be a better way, a right way to fix this problem??
Any suggestions??
The import always failed due to the Date fields error:
TransformCopy 'DirectCopyXForm' conversion error: Conversion invalid for datatypes on column pair 27 (source column 'Col027' (DBTYPE_STR), destination column 'last_modified' (DBTYPE_DBTIMESTAMP)).....
My current solution, is to convert those fields to varchar and then import the file, then reconvert them back to datetime. It works, but there has to be a better way, a right way to fix this problem??
Any suggestions??
ASKER
Here is a sample of the date data that is being imported:
Nov 10 2005 2:57:42:000PM : I format the field in the destination table as datetime but DTS will not work.
If I convert to varchar then import it works. Then afterwards, I have to convert back to datetime.
I hope this is enough info. I can post more if needed.
Nov 10 2005 2:57:42:000PM : I format the field in the destination table as datetime but DTS will not work.
If I convert to varchar then import it works. Then afterwards, I have to convert back to datetime.
I hope this is enough info. I can post more if needed.
If you can lose the milliseconds than you use some ActiveX function like this:
Function GetDate (Byval Dat)
Dim Dat
Dat = Left (Dat, 20) & Mid(Dat, 25, 2) 'Result: MMM dd yyyy hh:nn:ss?M
If IsDate(Dat) Then
GetDate = CDate(Dat)
Else
GetDate = Null
End If
You can then call it as follows:
DTSDestination("YourDateCo
Function GetDate (Byval Dat)
Dim Dat
Dat = Left (Dat, 20) & Mid(Dat, 25, 2) 'Result: MMM dd yyyy hh:nn:ss?M
If IsDate(Dat) Then
GetDate = CDate(Dat)
Else
GetDate = Null
End If
You can then call it as follows:
DTSDestination("YourDateCo
What is happening is that the Date variant in VBScript does not support milliseconds. If the milliseconds are important to you than you will have to import the table as a varchar and then convert to a datetime using T-SQL functions.
In order to convert from a string with milliseconds to a datetime using T-SQL you would use something like this:
CONVERT(datetime, 'Nov 10 2005 2:57:42:123PM', 109)
CONVERT(datetime, 'Nov 10 2005 2:57:42:123PM', 109)
There is a transform of type 'DateTime String' in DTS. Have a play with that.
Alternatively, I am successfully importing datetime data in that format using BULK INSERT so you may wish to try using that instead of DTS.
Sample usage:
BULK INSERT YourTable
FROM 'D:\YourFile.TXT'
WITH (
FIELDTERMINATOR = ',',
TABLOCK
)
The count and datatype of fields need to exaclty match though.
Alternatively, I am successfully importing datetime data in that format using BULK INSERT so you may wish to try using that instead of DTS.
Sample usage:
BULK INSERT YourTable
FROM 'D:\YourFile.TXT'
WITH (
FIELDTERMINATOR = ',',
TABLOCK
)
The count and datatype of fields need to exaclty match though.
ASKER
I'm going to try the activeX function, do I include it inside of the main function in the transformation script?
Alternatively, I have never used a bulk insert before, can you please specify?
I have around 30 fields of which 5 are datetime formats, thanks for the help.
Alternatively, I have never used a bulk insert before, can you please specify?
I have around 30 fields of which 5 are datetime formats, thanks for the help.
>>do I include it inside of the main function in the transformation script?<<
It would be separate of the Main function in the same ActiveX Script Task as in:
Function Main()
DTSDestination("YourDateCo
' Rest of your columns go here
Main = DTSTransformStat_OK
End Function
Function GetDate (Byval Dat)
Dim Dat
Dat = Left (Dat, 20) & Mid(Dat, 25, 2) 'Result: MMM dd yyyy hh:nn:ss?M
If IsDate(Dat) Then
GetDate = CDate(Dat)
Else
GetDate = Null
End If
It would be separate of the Main function in the same ActiveX Script Task as in:
Function Main()
DTSDestination("YourDateCo
' Rest of your columns go here
Main = DTSTransformStat_OK
End Function
Function GetDate (Byval Dat)
Dim Dat
Dat = Left (Dat, 20) & Mid(Dat, 25, 2) 'Result: MMM dd yyyy hh:nn:ss?M
If IsDate(Dat) Then
GetDate = CDate(Dat)
Else
GetDate = Null
End If
ASKER
sample:"
DTSDestination("columnx") = DTSSource("Col001")
Main = DTSTransformStat_OK
End Function
Function GetDate (Byval Dat)
Dim Dat
Dat = Left (Dat, 20) & Mid(Dat, 25, 2)
If IsDate(Dat) Then
GetDate = CDate(Dat)
Else
GetDate = Null
End If "
Error: "Activex Scripting Transform 'AxScriptXform': Error parsing script = Error Code: 0 Error Source=Microsoft VBScript compilation error Error Description" Name redefined "
Error on Line indicates the : "Function GetDate (Byval Dat)" line
DTSDestination("columnx") = DTSSource("Col001")
Main = DTSTransformStat_OK
End Function
Function GetDate (Byval Dat)
Dim Dat
Dat = Left (Dat, 20) & Mid(Dat, 25, 2)
If IsDate(Dat) Then
GetDate = CDate(Dat)
Else
GetDate = Null
End If "
Error: "Activex Scripting Transform 'AxScriptXform': Error parsing script = Error Code: 0 Error Source=Microsoft VBScript compilation error Error Description" Name redefined "
Error on Line indicates the : "Function GetDate (Byval Dat)" line
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sweet Thanks, It's importing...
Make sure to check after the import finalizes that the dates are converted correctly. If the function GetDate does not understand the date than it will convert to Null.
ASKER
Awesome, thank you its working, I'm kind of new to MS SQL .
Without knowing the date format and/or sample data we can only speculate.