June 3, 2015 at 6:49 am
Pretty straightforward - I have a column that I want to CAST as NULL if the LEN < 9
SQL to accomplish this:
update Temp
SET SSN = NULL
WHERE LEN(SSN) < 9
I try to use a Derived Column Transformation of:
LEN(SSN) < 8 ? NULL(DT_WSTR,11) : SSN
**EDIT - For some reason the HTML for < is displaying in my second bit of code instead of the actual character. & lt; = <
...and it does absolutely nothing.
I've tried changing the source and destination column datatypes from nvarchar to varchar with no effect. I'm kind of stumped as to why this is occurring, and I'd prefer to wrap this part in the Derived Column Transformation as running a OLE DB Command vs the database within the SSIS package takes longer to execute against the DB rather than processing this against the information that's already been read into memory.
Any ideas?
June 3, 2015 at 7:25 am
First, stupid question but why does your SQL have < 9 and your expression < 8?
Second, what happens when you replace the null function with a default string like "Null Test"? That should tell you if the problem is with your IF or with the NULL function.
June 3, 2015 at 8:36 am
Nevyn (6/3/2015)
First, stupid question but why does your SQL have < 9 and your expression < 8?Second, what happens when you replace the null function with a default string like "Null Test"? That should tell you if the problem is with your IF or with the NULL function.
1) Typo lol, I was trying to get the < to display properly on the second bit of code, rather than the HTML, and I must have fat fingered it.
2) Yep works just fine by doing "Null Test" or any other string, but the NULL Function won't. So the problem has to be with the NULL Function, but I have no idea why. The only thing I can think of is that I'm using Visual Studio 2008, but I can't find any notes online that would indicate this type of issue occurs with 2008.
June 3, 2015 at 8:42 am
....wth, I just got it to work.
Literally, I spent a good hour yesterday before heading home for the day trying to figure out why this wouldn't work and it just would not cooperate.
What I did to resolve? After having deleted the Derived Column a few times already from the Work Flow, I went ahead and created it by actually dragging and dropping each of the functions and the Columns into the actual Derived Column Transformation Editor from the list of 'Columns' & 'Functions' - and now it works.
What it looks like now, that it's working:
LEN(SSN) < 9 ? NULL(DT_WSTR,11) : SSN
What it looked like prior to this, when it was not working:
LEN(SSN) < 9 ? NULL(DT_WSTR,11) : SSN
Edit:
Well, where <: is <....still not certain why those are showing up as the HTML, and no idea how dragging and dropping the fields/functions would make any difference vs me typing what I wanted.
But it works, at least how I want it to, now...guess I should just drag and drop in the future :crazy:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply