Datetime problem in transform data task
-
I am importing a text file into a table using transform data task
I set the dates up to be date time strings in the transformations in the transform task properties and it all seems to work fine when the dates are full populated
however when there are no dates or a mixture of dates and no dates the step fails giving me a error message
"insert error column 9 ('Col009', DBTYPE_DBTIMESTAMP), status6: Data overflow. Invalid character value for cast specification"
does anyone have any ideas as to why this is happening and how I can fix the problem
any advice would be grateful
-
Date transformation in DTS is not very forgiving and either poorly formatted dates or low value years will cause problems.
In the transformation delete the 'copy column' transformation for the field and create a new one as activex and write code to validate the date.
Far away is close at hand in the images of elsewhere.
Anon. -
I have got this piece of code which I thought would make sure I would import dates and cope with null fields
' Copy each source column to the destination column
Function Main()
if DTSSource("Col009") = null then
DTSDestination("Col009") = null
else
DTSDestination("Col009") = DTSSource("Col009")
end if
DTSDestination("Col009") = mid(DTSSource("Col009"),1,4) & "/" & mid(DTSSource("Col009"),5,2) & "/" & mid(DTSSource("Col009"),7,2) & " " & mid(DTSSource("Col009"),9,2) & ":" & mid(DTSSource("Col009"),11,2)
Main = DTSTransformStat_OK
End Function
but when I run the step I get an invalid data error as im not that knowledgable in ActiveX script is there any thing obvious im doing wrong
any help would be great
-
Try it like this
if DTSSource("Col009") = null then
DTSDestination("Col009") = null
else
DTSDestination("Col009") = mid(DTSSource("Col009"),1,4) & "/" & mid(DTSSource("Col009"),5,2) & "/" & mid(DTSSource("Col009"),7,2) & " " & mid(DTSSource("Col009"),9,2) & ":" & mid(DTSSource("Col009"),11,2)
end if
Main = DTSTransformStat_OK
Far away is close at hand in the images of elsewhere.
Anon. -
I tried the code above the trouble now is when I run this all blank dates end up being / / : which is the formating bit with out the numbers is there anyway these can just be blank?
-
Just use the inbuilt DateTime transformation that comes with DTS. No need to fiddlw with ActiveXScripting.
--------------------
Colt 45 - the original point and click interface
-
Yea I have tried and I get error msgs from blank dates everythign is fine when the dates are fully populated but when they are not there in the source file its crashes the step
-
Stewart
Im no expert, but based on you results the first if statement is failing, try the following (and i make no promises)
if DTSSource("Col009") = '' then
DTSDestination("Col009") = ''
Note - 2 single quotes, not double quote.
Russ
-
No it didnt work I tried the single quotes and I get a error msg about the code and when I put double quotes I get invlaid data error msg when I run the step
everything keeps pointing back to when I dont have a date and it wont go in the datetime column
-
if DTSSource("Col009") = null then
DTSDestination("Col009") = null
elseif Trim(DTSSource("Col009")) = "" Then
DTSDestination("Col009") = null
else
DTSDestination("Col009") = mid(DTSSource("Col009"),1,4) & "/" & mid(DTSSource("Col009"),5,2) & "/" & mid(DTSSource("Col009"),7,2) & " " & mid(DTSSource("Col009"),9,2) & ":" & mid(DTSSource("Col009"),11,2)
end if
Main = DTSTransformStat_OK
BTW
What are the datatypes of DTSSource("Col009") and DTSDestination("Col009")
Far away is close at hand in the images of elsewhere.
Anon. -
I've used this for the same problem:
If ISDate( DTSSource("COL009") ) Then
DTSDestination("COL009") = CDate( DTSSource("COL009") )
Else
'Set your Default Date Here... or remove the entire Else section to leave the Destination Column as a DBNull.
DTSDestination("COL009") = Date()
End If
-Mike Gercevich
-
I didsomething similar when I had the same issue
Check if the date is a date,
if it is not set the value to today
else
dtsdestinantion = source
-
further, if you do not wish null values to be displayed as today, you could try setting the value to the 30/12/1799 00:00:00 this is the 1st/least value Sql server can accept, then when you are actually using the date values in your program just check for this date
-
This should do it :
MyDate = DTSSource("Col009")
if isnull(MyDate) then
MyDate = Null
Else
If Not isDate(MyDate) Then
MyDate = Null
Else
MyDate = Mid(MyDate, 1, 4) & Mid(MyDate, 5,2) & Mid(MyDate, 7,2) & " " & Mid(MyDate, 9,2) & ":" & Mid(MyDate, 11,2)
End If
End If
Main = DTSTransformStat_OK
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply