Solved

DTS Package Help - NULL values

Posted on 2007-11-19
6
541 Views
Last Modified: 2013-11-30
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!

0
Comment
Question by:EpiKAC
  • 2
6 Comments
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20314180
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
 

Author Comment

by:EpiKAC
ID: 20314712
No, here's one of the case statements:
CASE WHEN dbo.tblAmt.rcomenh <> '1' THEN 'arial' ELSE NULL END AS pScrollingTextFont
0
 
LVL 6

Expert Comment

by:Spot_The_Cat
ID: 20316620
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
 
LVL 6

Accepted Solution

by:
Spot_The_Cat earned 125 total points
ID: 20316644
PS. - the 'ELSE NULL' is not required.

eg.

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

provides the same result.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now