Hello

I have a text file with comma as column delimiter and CR+LF as RowDelimiter, from which I am transfering data to SQL server 2000 table. In that file, 2 columns does not have values. In the DTS Designer properties, I checked 'Allow Nulls' property. But still I am getting error as follows
----
Error during Transformation 'DTSTransformation__1' for Row number 1. Errors encountered so far in this task: 1.
Error Source: Microsoft Data Transformation Services (DTS) Data Pum
Error Description:ActiveX Scripting Transform 'DTSTransformation__1' encountered an invalid data value for 'Spa' destination column
Error Help File:sqldts80.hl
Error Help Context ID:3063
------

Can anyone help me how to solve this Your help is appreciated

Thanks in advance
RK

Can you show us the defintion of your entity and the row of data in the text
file that fails please.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"RK" <anonymous (AT) discussions (DOT) microsoft.com> wrote

that file, 2 columns does not have values. In the DTS Designer properties,
I checked 'Allow Nulls' property. But still I am getting error as follows:

Allan

1.My Table contains column name 'Spa' of decimal (10,4) datatype

2. My text file is somewhat like this
57, 114,MAJ PETERS SW&SOUR MIX RTU ,1L ,012,G, .0000,002, 232, , 1,6,61,02,00,0002440047770, , ,0002440047770, .0000, .0000, 13.5833, .0000, .0000, .0000, .0000, .0000, 2.0000, .0000, 2.00, 26.79, 3.28,0,0,0, 20.40, ,* , .20, .00,0,0,0,0,0,0, , , , , , , , , , , , , , ,READY TO USE , , , , , , , , , , ,Y,

I put * in the error column of the text file for easy understanding for you. There no data is present in place of *

Hope the things are clear

Thanks in advance
R

----- Allan Mitchell wrote: ----

Can you show us the defintion of your entity and the row of data in the tex
file that fails please

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP
www.SQLDTS.com - The site for all your DTS needs
I support PASS - the definitive, global communit
for SQL Server professionals - http://www.sqlpass.or


"RK" <anonymous (AT) discussions (DOT) microsoft.com> wrote in messag
news:76581198-E198-4686-A0F6-AFC6DCCAB3C7 (AT) microsoft (DOT) com..
that file, 2 columns does not have values. In the DTS Designer properties
I checked 'Allow Nulls' property. But still I am getting error as follows

OK so what I think is happening here is that the ,, is being interpreted as
"" i.e. an empty string. Try this and you'll see why I think this

declare @spa decimal(10,4)
declare @val varchar(5)
SET @val = '' --empty string
select @spa = CAST(@val as decimal(10,4))


I would therefore bring the text file into a Working table that has for that
attribute a Varchar() datatype. You can then use TSQL

So for my earlier example it would be

declare @spa decimal(10,4)
declare @val varchar(5)
SET @val = '' --empty string
SET @spa = CASE @val
WHEN '' then 0 ELSE @val End
print @spa


--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"RK" <anonymous (AT) discussions (DOT) microsoft.com> wrote

,0002440047770, .0000, .0000, 13.5833, .0000, .0000,
..0000, .0000, .0000, 2.0000, .0000, 2.00, 26.79,
3.28,0,0,0, 20.40, ,* , .20, .00,0,0,0,0,0,0, , , , , , , , , , ,
, , , ,READY TO USE ,
, , , , , , , , , ,Y,Y