February 22, 2006 at 7:06 am
I keep getting this error message when importing data from a .csv file: Insert error, column 7 ('VFrom', DBTYPE_DBTIMESTAMP), status 6: data Overflow. Invalid Character value for cast specification.
The source date is in format dd.MM.yyyy Cdate converts it to MM/dd/yyyy but SQL doesnt error with this format. I have tryed converting it to other date formats such as dd/MM/yyyy and yyyy-MM-dd but still get the same error message...can anyone help!!?
'************************************************************************
Function DateConvert(DateIn)
dim TempString
TempString = Left(DateIn,2) & "."
TempString = TempString & Mid(DateIn,4,2) & "."
TempString = TempString & Right(DateIn,4)
DateConvert = CDate(TempString)
End Function
Function Main()
DTSDestination("CndTyp") = DTSSource("Col001")
DTSDestination("CndTbl") = DTSSource("Col002")
DTSDestination("SalesOrg") = DTSSource("Col003")
DTSDestination("DistChnl") = DTSSource("Col004")
DTSDestination("Div") = DTSSource("Col005")
DTSDestination("PLTyp") = DTSSource("Col006")
DTSDestination("VFrom") = DateConvert(DTSSource("Col007"))
DTSDestination("VTo") = DateConvert(DTSSource("Col008"))
DTSDestination("IPC") = DTSSource("Col009")
DTSDestination("Price") = DTSSource("Col010")
DTSDestination("Cur") = DTSSource("Col011")
DTSDestination("MG2") = LTrim(DTSSource("Col012"))
DTSDestination("ID") = _
DTSDestination("CndTyp") & ":" & _
DTSDestination("CndTbl") & ":" & _
DTSDestination("SalesOrg") & ":" & _
DTSDestination("DistChnl") & ":" & _
DTSDestination("Div") & ":" & _
DTSDestination("PLTyp") & ":" & _
DTSDestination("VFrom") & ":" & _
DTSDestination("IPC") & ":" & _
DTSDestination("Cur")
Main = DTSTransformStat_OK
End Function
February 22, 2006 at 1:04 pm
What is the table definition of the destination?
If the VFROM is a timestamp column, then this a not a date and can not be imported.
February 22, 2006 at 11:59 pm
If the source was a timestamp data type from another Database vendor it most likely will fail. Editing at the source to make it acceptable to SQL Server is your only option.
If the value in your csv not really a SQL date say "02/02/1106" (the year wont be accepted) you'll get the same error.
if looking closely at the source fails just load it into a dummy table with the date columns as text (varchar), having an identity column (you can add it later). Execute Select idcolumn, isdate(thedatecolumn) with a where clause to show the offending rows.
Hint; ISDATE returns 1 if the input expression is a valid date; otherwise, it returns 0
Worked for me.
DB
The systems fine with no users loggged in. Can we keep it that way ?br>
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply