DTS Package Help - NULL values

I have a DTS package that is taking a view and writing it to a .txt file. It should be very straight forward but the .txt file is showing some weird results when dealing with NULL values.

In my view, some of the columns have text, some of the columns are always NULL, and some have a CASE statement that determines whether there is text in the field or if the value is NULL. The view looks fine. All fields with NULL values show up that way.

However, when the DTS package writes the view to the text file I have the problem. The fields with text are written properly, the fields with NULL are also presented properly. However, when the fields that have been determined by the CASE statement are written, if the field has been determined to be NULL it gets written with text delimiters '' instead of completely NULL. This does not happen with the other fields.

What am I missing? How do I get the DTS package to write the NULL fields to the text file without the text delimiters?

Thanks!

EpiKACAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Spot_The_CatConnect With a Mentor Commented:
PS. - the 'ELSE NULL' is not required.

eg.

CASE WHEN dbo.tblAmt.rcomenh <> '1' THEN 'arial' END AS pScrollingTextFont

provides the same result.
0
 
digital_thoughtsCommented:
It sounds like the CASE statement return isn't returning a NULL value, but rather a blank '' value, are you returning NULL is the case statement?

CASE WHEN Value='Something' THEN NULL ELSE Value END
0
 
EpiKACAuthor Commented:
No, here's one of the case statements:
CASE WHEN dbo.tblAmt.rcomenh <> '1' THEN 'arial' ELSE NULL END AS pScrollingTextFont
0
 
Spot_The_CatCommented:
Hi,

When I test this I find that any fields in the DTS's text file  that are defined as quoted come out quoted irrespective of whether they have a case statement.

In the example I just tried, the first record with <NULL> came out as "" in the text file and the second record as "text". My CASE statement does exactly the same unless I define the column as non quotable.

So maybe I've misunderstood your query but that's exactly the behaviour I'd expect to see.

Spot
0
All Courses

From novice to tech pro — start learning today.