October 19, 2010 at 11:05 am
Data is formatted like so: 20050121, 20101019, etc. 8 digits, no punctuation, YYYYMMDD.
Derived column conversion does:
REPLACE(SUBSTRING(effDt,5,2) + "/" + SUBSTRING(effDt,7,2) + "/" + SUBSTRING(effDt,1,4),"//","")
I want the end "" to be NULL, but it doesn't like it when I use NULL(DT_Str, 10, 1252) or when I try to use NULL(DT_DBTimestamp). Goes all red and long error messagey with a "unable to convert due to data loss".
I tried using:
ISNULL(effDt)==True ? effDt : SUBSTRING(effDt,5,2) + "/" + SUBSTRING(effDt,7,2) + "/" + SUBSTRING(effDt,1,4) but it kept in the slashes, returning // instead of NULL.
Any thoughts as to what I'm missing? I know it's something basic, but it's gotten to the point where I'm staring at it too long.
October 19, 2010 at 12:36 pm
I can't immediately find the "error" either.
But what are you trying to accomplish?
Or you trying to format a string to another string that looks like a date, or are you formatting the string to a date?
I would opt for the second expression, but maybe there are some records in your data that just contain spaces, so they pass the "not null" test.
Maybe also include the following test: (LEN(TRIM(myColumn)) == 0)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 19, 2010 at 1:47 pm
How good is your data quality? Assuming it's very good (so either valid 8 digit integer representing yyyymmdd or NULL) then you could try (psuedo code)
if date is null or length(date) == 0 then null(dt_date) else (dt_date) (SUBSTRING(date,1,4) + "-" + SUBSTRING(date,5,2) + "-" + SUBSTRING(date,7,2))
from the above, note you can force a null using the Null(<valid_datatype>) function and according to BOL, string is a legal conversion to datetime, the sample shows using '-' rather than '\' though. Like wa sjust mentioned, you may need to check for zero length strings/ints as well as Null.
Steve.
October 20, 2010 at 4:43 am
Steve,
I don't think IF...THEN...ELSE works in SSIS. That's why I'm trying to use the ? : conditional. And it's not evaluating correctly...
I'm trying to format an 8 character string into a date. The data quality is good. It's either a date or it's NULL. But for some reason, my conditional isn't working... It doesn't pick up the NULLS correctly.
da-zero, The file is tilda delimited. There are no spaces between tildas. It's either ~20100601~ or ~~, which is NULL. I'm doing the Derived column straight from that, so it's not importing the data into a staging table and switching NULLS to Spaces (which I have had happen before).
I guess I'll need to add a second Derived Column task to switch out all the // results, but I know I shouldn't have to. I'm missing something. I just don't remember what.
EDIT: Really am braindead this morning. Just caught your comment on using Len(), Steve. Thank you. I'll have to see if I can make that work.
October 20, 2010 at 4:51 am
This is the expression from Steve in "derived column language" (aka put everything on one stupid line language):
(ISNULL(effDt) || LEN(TRIM(effDt)) < 8) ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING(effDt,5,2) + "/" + SUBSTRING(effDt,7,2) + "/" + SUBSTRING(effDt,1,4))
I still added the check for the length, just to make sure 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 20, 2010 at 5:00 am
Now see, I've tried the ISNULL(effDt) without the == TRUE and while it didn't "bleed", it did error out on me. But then again, I haven't tried the entire line written the way you just did.
I'll add it to the list of possible solutions as soon as I'm done catching up on morning email.
October 20, 2010 at 5:21 am
I never use the == TRUE(/FALSE) with the ISNULL function. There is not really a point comparing a boolean value with another boolean value, so I just take the original boolean value. This never generated an error for me.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 20, 2010 at 5:48 am
It was causing all sorts of errors for me, though. Too many to count. I was trying all sorts of variations on the expression.
LEN() was the magic bullet. I ended up with:
LEN(effDt) == 0 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING(effDt,5,2) + "/" + SUBSTRING(effDt,7,2) + "/" + SUBSTRING(effDt,1,4))
and it loaded into my table fine. Now I just wish I could get the Bulk Insert Task to work so I could do this faster.
...except I may have just figured out why the Bulk Insert Task isn't working... More experimentation needed. WHOO.
October 20, 2010 at 6:02 am
Brandie Tarvin (10/20/2010)
...except I may have just figured out why the Bulk Insert Task isn't working... More experimentation needed. WHOO.
Great, it seems that you are getting there 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 20, 2010 at 6:06 am
.... you could try (psuedo code) ....
Hey Brandi - pseudo code means just that 🙂 I'm not a huge fan of the SSIS scripting language so it was easier to short hand it all out. Thank DZ for doing the heavy lifting and translating it 🙂
Glad you got it all working though!
Steve.
October 20, 2010 at 6:09 am
Grateful to both of you for helping out. Thanks!
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply