Datetime problem in transform data task

Question

Datetime problem in transform data task

stewart Neale

SSC Enthusiast

Points: 124
More actions
June 30, 2005 at 3:41 am

#186439

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

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65111 More actions · Answer 1

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.

stewart Neale SSC Enthusiast Points: 124 More actions · Answer 2

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

David Burrows SSC Guru Points: 65111 More actions · Answer 3

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.

stewart Neale SSC Enthusiast Points: 124 More actions · Answer 4

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?

philcart SSC-Forever Points: 47794 More actions · Answer 5

Just use the inbuilt DateTime transformation that comes with DTS. No need to fiddlw with ActiveXScripting.

--------------------
Colt 45 - the original point and click interface

stewart Neale SSC Enthusiast Points: 124 More actions · Answer 6

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

Russell Woodham SSC Veteran Points: 243 More actions · Answer 7

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

stewart Neale SSC Enthusiast Points: 124 More actions · Answer 8

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

David Burrows SSC Guru Points: 65111 More actions · Answer 9

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.

mike gercevich SSCrazy Points: 2961 More actions · Answer 10

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

jon-214566 Grasshopper Points: 14 More actions · Answer 11

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

jon-214566 Grasshopper Points: 14 More actions · Answer 12

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

Habib Zmerli Mr or Mrs. 500 Points: 559 More actions · Answer 13

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

Main = DTSTransformStat_OK

Kindest Regards,

Habib Zmerli (MVP)

Habib Zmerli Mr or Mrs. 500 Points: 559 More actions · Answer 14

Sorry, Iforgot the line :

DTSDestination("col009")=MyDate

before the function return

Kindest Regards,

Habib Zmerli (MVP)