May 16, 2008 at 3:06 pm
This is driving me nuts. I've re-written this particular split condition 3 times and it still won't pick up the date.
Date is 01/11/5200. I'm using smalldatetime datatype in my staging table. The conditional split is supposed to send the invalid date record to a text file instead of a staging table. Here's the condition:
ISNULL(RetroDate) == FALSE && (RetroDate (DT_DATE)"2079-06-06")
Now I did have the dates listed "01/01/1900" and "06/06/2079" but that didn't work either. Then I put the (DT_DATE) conversion in front of the RetroDate field to double-check the conversion was the same, but that didn't work either...
ARGH.
Thoughts appreciated. Thanks in advance.
May 16, 2008 at 4:24 pm
Brandie Tarvin (5/16/2008)
This is driving me nuts. I've re-written this particular split condition 3 times and it still won't pick up the date.Date is 01/11/5200. I'm using smalldatetime datatype in my staging table. The conditional split is supposed to send the invalid date record to a text file instead of a staging table. Here's the condition:
ISNULL(RetroDate) == FALSE && (RetroDate (DT_DATE)"2079-06-06")
Now I did have the dates listed "01/01/1900" and "06/06/2079" but that didn't work either. Then I put the (DT_DATE) conversion in front of the RetroDate field to double-check the conversion was the same, but that didn't work either...
ARGH.
Thoughts appreciated. Thanks in advance.
Brandie,
I'm not sure if you've missed an operator here (looks like the greater than maybe missing from the right side), but you've got a syntax error somehow.
Either way, I think your logic error may be in the && in the middle. You're testing RetroDate for NULL and doing a comparison against the future date; using the && means that both conditions must be true which could never happen.
Try something like this...
ISNULL(RetroDate) == FALSE || (DT_DATE)"06/06/2079" < (DT_DATE)RetroDate
Hope this helps...
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
May 16, 2008 at 4:38 pm
I didn't look at my code before I submitted the post. The forum screwed it up. Let me try again.
ISNULL(RetroDate) == FALSE && (RetroDate lessthan (DT_DATE)"01-01-1900"
|| RetroDate greaterthan(DT_DATE)"06-06-2079")
Wow. Are there any escape characters in the new forum which can prevent the angle brackets from being treated as IFCode?
May 16, 2008 at 5:15 pm
Brandie Tarvin (5/16/2008)
I didn't look at my code before I submitted the post. The forum screwed it up. Let me try again.
ISNULL(RetroDate) == FALSE && (RetroDate lessthan (DT_DATE)"01-01-1900"
|| RetroDate greaterthan(DT_DATE)"06-06-2079")
Wow. Are there any escape characters in the new forum which can prevent the angle brackets from being treated as IFCode?
OK, I see what you meant. Replace the && (two ampersands) after your first test with || (two pipe symbols). That way your testing for null OR a date earlier than expected OR a date later than expected.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
May 16, 2008 at 5:26 pm
As soon as my current code run quits, I'll try that, buuuttt.....
Are you saying the parens around the OR part of the statement aren't working correctly? I put in the ISNULL() check for FALSE because the code was failing the check when RetroDate was NULL and couldn't be compared to an actual date.
The T-SQL version of what I used in the conditional would/should be:
WHERE RetroDate IS NOT NULL
AND (
RetroDate lessthan '01/01/1900'
OR RetroDate greaterthan '06/06/2079'
)
Which does what I want it to do in T-SQL, but not so much in the conditional split...
Still, I'll try testing for ISNULL() equals True with an OR instead of a False / AND.
May 16, 2008 at 5:43 pm
Well, lesson learned - I shouldn't try to dispense advice when my mind is on getting out the door on Friday afternoon... You're right, the code is correct as posted.
I just created a conditional split with the same syntax you posted, with the exception that I explicitly cast the RetroDate to a DT_DATE. I'm not getting the same results you got; I used your stated date of 01/11/5200 and this one passed to the alternate output while others (with valid dates) went through to the default output without error.
I'm stumped on this one, Brandie - I'm not sure what's happening with this transform. Can you post a snippet from your source file, and maybe a screen shot from the conditional split editor?
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
May 19, 2008 at 5:51 am
I had the same result as Tim, I re-created this and the original logic worked fine.
Check your data types on your source data in the Advanced Editor to make sure your date field is coming over as the correct type.
May 20, 2008 at 8:18 am
Hey guys just a thought you've got '..NULL AND .....' er are you sure that is correct
Should it not be (.....NULL) AND (......)?
Have I missed the point?
Also in the previous logic you had ...FALSE &&... as well.
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
May 20, 2008 at 8:45 pm
your code looks good to me as well and Ive done a very similiar thing and it worked. Im sure this is a stupid reply but in case its the old 'is it plugged in' response, youre sure your using the correct output constraint and not the default right? 🙂
May 27, 2008 at 11:01 am
The code is correct, however, I think (and I haven't had a chance to test it yet because I'm solving other problems) the issue is the difference between the datatype of DT_DATE and DT_DBTIMESTAMP. When I looked at the original metadata coming out of my OLE DB Source, even though the source database had the column set as DATETIME, SSIS read the datatype as DBTIMESTAMP. Weird much?
So, I've reset the output datatype to DATE and as soon as I get a chance to go back and re-run that section of code, we'll see if the conditional split actually works properly now that I'm comparing apples to apples.
May 27, 2008 at 11:06 am
Shaun,
I think you might be missing the point slightly.
I don't need parens around the first part of the statement because that statement will always be true and I want it to apply to either of the OR statements that parse out. There's no need to waste parens on the (...NULL) and even if SQL Server kept them in, it's an additional pre-validation parse to put in unnecesary parens.
I could be mistaken, but I believe the engine would have to do "extra" work to parse every paren'd statement to verify precedence and keep things together. And since both statements work out the same whether or not my ISNULL() is in its own parens, why take the risk of adding extra workload to my package?
Again, that's assuming that SQL doesn't just delete the parens (like it does in the Query Designer) for that part of the statement automatically.
May 27, 2008 at 11:06 am
Actually, the DT_DBTIMESTAMP in SSIS really is DATETIME in SQL Server, not TIMESTAMP. MS has been wonderfully consistent here.
This is a good place to start though as the data types being different can be a real problem even if the two types "should" convert correctly.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply