Reading sequential file empty columns NULL vs. Empty String
Posted: Wed Feb 18, 2009 5:32 pm
Hi,
I'm reading a tilde delimited file like so:
Col1~Col2~Col3 << header,
A~~C
So, Col2 is missing.
I need to validate the data in Col2 with data in another table (SQL Server, I'll call this table Lookup, and the lookup column Col2_NK). So, I use a reference lookup in the transformer.
Both columns have the format char(6), which is the column type of Col2_NK in the Lookup table.
My problem is a blank value is allowed in the incoming data. My data validation rule is "a blank value is allowed only if the data in table blah has a blank value", which it does in this case. Strange that blank is allowed, but there you go.
My problem is the lookup never gets a hit, and all blank values from the input data get rejected.
I know the problem by walking thru the code in the debugger: Col2 = NULL when read from the sequential file, and Col2_NK = 6 spaces in the lookup table.
So, my questions:
1. What is the best practice for dealing with this issue? Random (untested) thoughts:
* Proper configuration in the format tab of the sequential file
* Cast both columns to varchar
* Fiddling with TRIM functions in the lookup
I was hoping someone could recommend best practice rather than me hacking around until I get something to "work".
2. In the format tab of the sequential file output link, what's the difference between "Map empty string" vs. "Pad with empty string"?
3. What's the default padding field used for, and does it apply here?
(I have read the help file on all these tabs and skimmed the doc before posting. Either I'm not finding the proper hits or just not getting what the help file is trying to say.)
Thanks,
Scott
I'm reading a tilde delimited file like so:
Col1~Col2~Col3 << header,
A~~C
So, Col2 is missing.
I need to validate the data in Col2 with data in another table (SQL Server, I'll call this table Lookup, and the lookup column Col2_NK). So, I use a reference lookup in the transformer.
Both columns have the format char(6), which is the column type of Col2_NK in the Lookup table.
My problem is a blank value is allowed in the incoming data. My data validation rule is "a blank value is allowed only if the data in table blah has a blank value", which it does in this case. Strange that blank is allowed, but there you go.
My problem is the lookup never gets a hit, and all blank values from the input data get rejected.
I know the problem by walking thru the code in the debugger: Col2 = NULL when read from the sequential file, and Col2_NK = 6 spaces in the lookup table.
So, my questions:
1. What is the best practice for dealing with this issue? Random (untested) thoughts:
* Proper configuration in the format tab of the sequential file
* Cast both columns to varchar
* Fiddling with TRIM functions in the lookup
I was hoping someone could recommend best practice rather than me hacking around until I get something to "work".
2. In the format tab of the sequential file output link, what's the difference between "Map empty string" vs. "Pad with empty string"?
3. What's the default padding field used for, and does it apply here?
(I have read the help file on all these tabs and skimmed the doc before posting. Either I'm not finding the proper hits or just not getting what the help file is trying to say.)
Thanks,
Scott