sql server dts: H

I´ve had some problems importing text files in different servers that really confused me

I´ve installed SQL SERVER and built a database (using a script) to make it identical to the one I had in production server. The weird thing is that certain text files used at production server could not be imported to this new server

Let me give more details about it. Consider the following tabl

CREATE TABLE X
[col1] [varchar] (50) NULL
[col2] [varchar] (50) NULL
[col3] [int] NULL
[col4] [int] NUL
) ON [PRIMARY

Suppose there is a delimited text file that has to be imported to this table. Considering the semicolon as column delimiter and no text qualifier, the file content could be like this

text ; text ; 1 ; 1
text ; ; ; 2

Notice the 2nd register has 2 fields with null content for columns 2 and 3. Also observe that the corresponding fields in table X should accept null values

Anyway: when I import this sample file to table X using DTS, there is the following error message

ERROR .....
TransformCopy 'DirectCopyXform' conversion error: Conversion invalid for datatypes on column pair 3 (source column 'Col003' (DBTYPE_STR) , destination column 'col3' (DBTYPE_I4)

Funny thing is that this datatype convertion works fine in production server. By the way, at this new installation, all datatype conversation involving NULLs will fail, because all fields in the text file are considered STRINGs (DBTYPE_STR)

Since I was not the one that installed SQL SERVER at this production server, I wonder I´m missing a configuration parameter that should make things work out

Any ideas

Thanks in advance

In your table structure, change col3 and col4 to varchar instead of int.
The error is because it is trying to copy spaces in row 2 to the int
column.

Regards

Partha Mandayam
Software Consultant
Home page: http://partha.tripod.com


*** Sent via Developersdex http://www.developersdex.com ***

Actually, it occured to me to do it.
But why did it work before?

Wagne

----- Partha Mandayam wrote: ----

In your table structure, change col3 and col4 to varchar instead of int
The error is because it is trying to copy spaces in row 2 to the in
column

Regard

Partha Mandaya
Software Consultan
Home page: http://partha.tripod.co


*** Sent via Developersdex http://www.developersdex.com **
Don't just participate in USENET...get rewarded for it

The file may not have had spaces before, hence it worked.

Regards

Partha Mandayam
Software Consultant
Home page: http://partha.tripod.com


*** Sent via Developersdex http://www.developersdex.com ***