Converting data Types with Active X transformation

Question

Converting data Types with Active X transformation

al_nick

Say Hey Kid

Points: 713
More actions
July 12, 2005 at 2:20 pm

#165411

Hi there,
I'm have a simple tab delimited txt file I'm attempting to import into a sql table using DTS. However, I'm having touble copying the columns from the file to the table due to data type mismatches. The vachar columns come over fine by just copying as-is. However the integer and date column (which a simple strings in the file) are giving me a 'Type Mismatch: Cint and Type Mismatch: CDate' errors when I try to script the transform using ActiveX. Is it becuase I'm pulling from a file?
Any and all help would be MUCH appreciated,
al

Here's my script for a single column transform:
Function Main()
Dim strSessionID
strSessionID = DTSSource("Col002")
DTSDestination("SessionID") = CInt(strSessionID)
Main = DTSTransformStat_OK
End Function
This of course works:
Function Main()
Dim strSessionID
strSessionID = "12345"
DTSDestination("SessionID") = CInt(strSessionID)
Main = DTSTransformStat_OK
End Function

Viewing 5 posts - 1 through 4 (of 4 total)

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

dm Ten Centuries Points: 1135 More actions · Answer 1

Instead of trying to convert the data from a string to a Date/Integer datatype during the import, you could import the file into a table with all text fields (including the date and Integer fields) then do a insert into a 2d table (set up with the correct datatypes for each field) and use the Convert or Cast function to convert the data type. Worked for me in the past...

Diane

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

You shouldn't need to have any script to transform numbers and dates. The ints should be fine with implicit conversions and there is a DateTime transformation that specifically handles importing dates.

Are you able to check all the rows in the file and make sure that those fields have valid data in them? The previous posters suggestion of loading the data into varchar fields would be your best course of action if you can't rely on the quality of the data.

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

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

Are these null values, or illegal characters?

You can try to check the values with IsDate and IsNumeric before doing CInt or CDate.

Function Main()

Dim strSessionID

strSessionID = DTSSource("Col002")

If IsNumeric(strSessionID) Then

DTSDestination("SessionID") = CInt(strSessionID)

Else

DTSDestination("SessionID") = Null

End If

Main = DTSTransformStat_OK

End Function

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 4

CInt converts to a smallint, could that be the problem, if your SessionID is an int then use CLng instead of CInt.

Andy