Re: DTS bug (?): Data Driven Query Task incorrectly replaces empty strings with NULL.

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 06/22/04 

Date: Tue, 22 Jun 2004 18:23:41 +0100

Yep the '' is transferred as a NULL

Change the VARCHAR to a CHAR and the problem goes away.

I am not sure why at the moment but it would look to be something that is
being interpreted in the Active Script as a straight COPY --> COPY using a
DataPump task works as expected so it is not something that DTS cannot
handle.

I will do some digging 

-- 
-- 
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
"John Peterson" <j0hnp@comcast.net> wrote in message
news:OSdXTdHWEHA.2972@TK2MSFTNGP12.phx.gbl...
> Allan, could you try your test with a varchar(50) instead of a char(1) to
see if it makes
> any difference?  That's what my definition is.
>
> Thanks!
>
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:uUZ1VjFWEHA.556@tk2msftngp13.phx.gbl...
> > OK in the absence of schema and sample data I created what I think is a
> > repro and my results differ from John's
> >
> > CREATE TABLE DDQ_NULL_SOURCE(col1 int primary key, colNullableCharCol
> > char(1) NULL)
> > GO
> > CREATE TABLE DDQ_NULL_DEST(col1 int, colNullableCharCol char(1) NULL)
> > GO
> > CREATE PROCEDURE InsertInto_DDQ_NULL_SOURCE_i @i int, @c char(1)
> > AS
> > INSERT DDQ_NULL_DEST(col1, colNullableCharCol) VALUES(@i, @c)
> > GO
> > INSERT DDQ_NULL_SOURCE(col1, colNullableCharCol) VALUES(1,'')
> > GO
> > INSERT DDQ_NULL_SOURCE(col1, colNullableCharCol) VALUES(2,NULL)
> > GO
> > SELECT * FROM DDQ_NULL_SOURCE
> > GO
> > --Results
> > --col1          colNullableCharCol
> > ----------- ------------------
> > --1
> > --2             [NULL]
> >
> > --After DTS
> > --Results
> > --SELECT * FROM DDQ_NULL_DEST
> > --col1          colNullableCharCol
> > ----------- ------------------
> > --1
> > --2             [NULL]
> >
> > -- 
> >
> > ----------------------------
> >
> > 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
> >
> >
> > "John Peterson" <j0hnp@comcast.net> wrote in message
> > news:uWluvXFWEHA.3472@TK2MSFTNGP09.phx.gbl...
> > > Thanks Mark!  But I *really* need to differentiate between NULLs and
empty
> > strings -- I
> > > can't have them all be NULL or all be empty strings.  I'm *very*
surprised
> > that the Data
> > > Driven Query Task behaves this way.  It seems to propagate *other*
NULLs
> > correctly,
> > > including NULL strings.
> > >
> > >
> > > "mark" <mark@remove.com> wrote in message
> > > news:c6TBc.22$Nx2.9@newsfe3-win.server.ntli.net...
> > > >
> > > > "John Peterson" <j0hnp@comcast.net> wrote in message
> > > > news:OvBMir9VEHA.1012@TK2MSFTNGP09.phx.gbl...
> > > > > (SQL Server 2000, SP3a)
> > > > >
> > > > > Hello all!
> > > > >
> > > > > I have a DTS package that uses the Data Driven Query Task (DDQT)
to
> > invoke
> > > > a stored
> > > > > procedure on the Insert action (that's the only action that's
> > defined).
> > > > My source table
> > > > > has a column that I'm using as the value to one of the parameters
to
> > the
> > > > stored procedure.
> > > > > Sometimes I have explicit NULLs in this column, sometimes I have
an
> > empty
> > > > string ('').
> > > > >
> > > > > For some reason, going through DDQT is *always* converting my
empty
> > > > strings to NULLs when
> > > > > passing the value into the stored procedure.  Is there any way to
> > retain
> > > > the original
> > > > > value (empty string)?
> > > > >
> > > > > Thanks!
> > > > >
> > > > > John Peterson
> > > > >
> > > > this happened to me - didnt fix it but i was advised to change nulls
> > using
> > > >
> > > > UPDATE SomeTable  SET col1 = COALESCE(col1, ''), coll2 =
COALESCE(col2,
> > ''),
> > > > ... WHERE col1 IS NULL OR col2 IS NULL etc using query analyzer -
not
> > ideal
> > > > but an option
> > > >
> > > >
> > > > mark
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Variable text file destination name
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... www.SQLDTS.com - The site for all your DTS needs. ... >>Allan Mitchell MCSE,MCDBA, ... >>> destination dynamically to Access or SQL databases I ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS bug (?): Data Driven Query Task incorrectly replaces empty strings with NULL.
    ... DataPump task works as expected so it is not something that DTS cannot ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... >> Allan Mitchell MCSE,MCDBA, ...
    (microsoft.public.sqlserver.dts)
  • Re: importing JUST data from Access 97 into SQL Server 2000
    ... > Allan Mitchell MCSE,MCDBA, ... > www.SQLDTS.com - The site for all your DTS needs. ... Workgroup information file is ... >> SQL Server instead, without keys, indexes, relationship. ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS Field Lenght
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... www.SQLDTS.com - The site for all your DTS needs. ... >> Allan Mitchell MCSE,MCDBA, ... >> I support PASS - the definitive, ...
    (microsoft.public.sqlserver.dts)
  • Re: import excel data error
    ... What will be happening is that although it looks empty to you it won't be to ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... www.SQLDTS.com - The site for all your DTS needs. ... > the same column as the excel file. ...
    (microsoft.public.sqlserver.dts)