Field Has No Value But Is Not NULL Or Empty

Question

Post reply

Field Has No Value But Is Not NULL Or Empty

Polymorphist

SSCommitted

Points: 1909
More actions
August 13, 2015 at 7:22 pm

#398897

Hello All,
I feel really dumb for asking this question, but here's my situation:
I added a new field to an existing ETL process which uses SSIS to ingest a CSV file. The new field in the file, Call_Transaction_ID, will not always be populated for every record and so can be NULL or empty for certain records.
Here's the problem:
After the file is extracted into a staging table, the Call_Transaction_ID field is showing blank or empty when it has no ID for that particular record. The problem is when I try to then ETL this data into a fact table - I'm trying to set the Call_Transaction_ID field to -1 if it is NULL or empty, however SQL Server doesn't see the field as empty even though there is no value in the field so -1 will NEVER return.
Using a WHERE DATALENGTH(Call_Transaction_ID) = 0 returns 0 records, again because SQL Server doesn't see the field as empty or NULL.
What do I do now to get around this? How do I fix it?
Thanks!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Polymorphist SSCommitted Points: 1909 More actions · Answer 1

Okay, so I kind of answered my own question here -

CASE WHEN LEN(Call_Transaction_ID) > 1 THEN call_transaction_ID ELSE -1 END

When I did a LEN(Call_Transaction_ID) on the field I get a value of 1 whenever there is no real ID (which is 17 characters). Thing is, I still don't know why the CSV file is inserting one character into the field in the table, i'd like to find that out in case it breaks something down the road. Handling this with a CASE statement seems sort of whonky to me...

Dung Dinh SSCrazy Points: 2513 More actions · Answer 2

Polymorphist (8/13/2015)
Hello All,
I feel really dumb for asking this question, but here's my situation:
I added a new field to an existing ETL process which uses SSIS to ingest a CSV file. The new field in the file, Call_Transaction_ID, will not always be populated for every record and so can be NULL or empty for certain records.
Here's the problem:
After the file is extracted into a staging table, the Call_Transaction_ID field is showing blank or empty when it has no ID for that particular record. The problem is when I try to then ETL this data into a fact table - I'm trying to set the Call_Transaction_ID field to -1 if it is NULL or empty, however SQL Server doesn't see the field as empty even though there is no value in the field so -1 will NEVER return.
Using a WHERE DATALENGTH(Call_Transaction_ID) = 0 returns 0 records, again because SQL Server doesn't see the field as empty or NULL.
What do I do now to get around this? How do I fix it?
Thanks!!

I think that the Call_Transaction_ID field is storing white spaces so DATALENGTH does not work. If you're using ETL, I suggest that you use Transformation components such as Derived Column to process Call_Transaction_ID instead of updating it in your database.

Thanks,

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Polymorphist (8/13/2015)
Okay, so I kind of answered my own question here -
CASE WHEN LEN(Call_Transaction_ID) > 1 THEN call_transaction_ID ELSE -1 END
When I did a LEN(Call_Transaction_ID) on the field I get a value of 1 whenever there is no real ID (which is 17 characters). Thing is, I still don't know why the CSV file is inserting one character into the field in the table, i'd like to find that out in case it breaks something down the road. Handling this with a CASE statement seems sort of whonky to me...

While we could assume that the staging table you are loading this value into is a character data type, that wouldn't really help us, as without knowing the exact data type, we'd have no shot at reproducing the behavior. We also don't know what the CSV file actually looks like either, and that's another necessity. While you may have gotten past the immediate crisis, I'd be concerned that maybe the design for this SSIS package isn't quite what it should be, and that you may be making a few unwarranted assumptions that you're just not yet aware of. If you can provide some sample data and a CREATE TABLE statement for the staging table involved, then folks here will have a shot at helping you. Lacking same, the best anyone can do is guess at the cause of the problem. Try to remember that we can't see what you're seeing, and be aware that you may see what you're saying as perfectly describing everything we might need to know, but most of the best helpers on this site will likely disagree with that perspective...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ScottPletcher SSC Guru Points: 101120 More actions · Answer 4

Most likely rather than a blank the first char in the column is some non-displayable character, such as a tab (char(9)), cr (char(13)), or line feed (char(10)).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Polymorphist SSCommitted Points: 1909 More actions · Answer 5

Hello All,

Thanks for the replies. Excellent advice on posting questions, i'll take heed of that going forward.

You're correct about the whitespace character, I actually went back and handled this in the derived column transformation (there was already one in this package) and it works okay now. I used a REPLACE() function.

Thanks!!