source txt file dts to datatype int in table.

[purpleplane]

I have a dts package that reads a text file and inserts it into a table in mydatabase the fields with datatype int are erroring out. I cant go from text to int in sql server. Would the best way to handle this be a temp table, making the int feilds char in the tmptable then insert into the table from the tmptable because char to int is explicit?

 

[JayKusch]

Can you use a convert on the columns that are to be Ints as in ...

CONVERT(Int,col012)

Thanks

J. Kusch

 

[purpleplane]

sql server wont allow convert from a text field to int.

 

[JayKusch]

If you are talking about CONVERTing a data type of "Text" to Integer, you are correct in saying the CONVERT will not work.

If you are saying a column w/in a "text file" that is set as an alphanumeric, you are incorrect and the CONVERT WILL WORK ...

example:

TextFile:
col01 col02 col03
'Jane Doe',1234598,"Addison Street"

SELECT CONVERT(Int, Col02)

Also as an example try this in Query Analyzer:

SELECT CONVERT(Int,'1234598')

Thanks

J. Kusch

 

[purpleplane]

This code is in the properties of the transformation tab and written in vb. there isnt a cast or convert available. I have tried several intrinsic vbscript functions to try and convert the type...just to no avail. Thanks for your input!

 

[nigelrivett]

What you are describing shouldn't happen. Int's will be impliciely converted.
It is more likely that something in the file is incorrect.

Can you post the format of the file and table.

For text file imports it is usually simpler to use bulk insert.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[john76]

Try using the CInt function:

Function Main()
  If IsNumeric(DTSSource("Field1")) Then
    DTSDestination("Field1") = CInt(DTSSource("Field1"))
  Else
    [green]' Whatever you want to do to handle this situation[/green]
  End If
  Main = DTSTransformStat_OK
End Function

HTH,
John