SSIS Derived column IF..ELSE condition
I have a column WEEK which is VARCHAR and has values like 201112,201135 in which last two characters represend the week no...so I need to create a derived column NEW_WEEK in such a way that if (cast( SUBSTRING( WEEK,5,2) as int)>=1 AND cast( SUBSTRING( WEEK,5,2) as int)<=17) Then NEW_WEEK=( cast( SUBSTRING( WEEK,5,2) as int)+17) ELSE (cast( SUBSTRING( WEEK,5,2) as int)-17) I am using following expression.. (((DT_I4) SUBSTRING( WEEK,5,2)>=1) && ((DT_I4) SUBSTRING( WEEK,5,2)<=17))?((DT_I4) SUBSTRING( WEEK,5,2)+35):((DT_I4) SUBSTRING( WEEK,5,2)-17)) Can somebody tell me whats wrong in it? I also tried.. IIF((DT_I4) [SUBSTRING( CALWEEK,5,2)]>=1 && (DT_I4) [SUBSTRING( CALWEEK,5,2)]<=17) then ((DT_I4) [SUBSTRING( CALWEEK,5,2)]+35) else ((DT_I4) [SUBSTRING( CALWEEK,5,2)]-17)) its also not working :(
July 30th, 2011 3:39am

what is the error you are getting??My Blog | Ask Me |
Free Windows Admin Tool Kit Click here and download it now
July 30th, 2011 4:13am

I have a column WEEK which is VARCHAR and has values like 201112,201135 in which last two characters represend the week no...so I need to create a derived column NEW_WEEK in such a way that if (cast( SUBSTRING( WEEK,5,2) as int)>=1 AND cast( SUBSTRING( WEEK,5,2) as int)<=17) Then NEW_WEEK=( cast( SUBSTRING( WEEK,5,2) as int)+17) ELSE (cast( SUBSTRING( WEEK,5,2) as int)-17) I am using following expression.. (((DT_I4) SUBSTRING( WEEK,5,2)>=1) && ((DT_I4) SUBSTRING( WEEK,5,2)<=17))?((DT_I4) SUBSTRING( WEEK,5,2)+35):((DT_I4) SUBSTRING( WEEK,5,2)-17)) Can somebody tell me whats wrong in it? I also tried.. IIF((DT_I4) [SUBSTRING( CALWEEK,5,2)]>=1 && (DT_I4) [SUBSTRING( CALWEEK,5,2)]<=17) then ((DT_I4) [SUBSTRING( CALWEEK,5,2)]+35) else ((DT_I4) [SUBSTRING( CALWEEK,5,2)]-17)) its also not working :( (DT_I4) SUBSTRING( WEEK,5,2) >=1 && (DT_I4) SUBSTRING( WEEK,5,2) <= 17 ? (DT_I4) (SUBSTRING( WEEK,5,2))+ 35 : (DT_I4) (SUBSTRING( WEEK,5,2))-17 I made a modification different from this expression which gave an error and now adjusted to the expression above. When i tired to paste this in Derived column expression, I am not able to do so due to weird behavior and I tried a lot but in vain, hope this expression resolves the issue.Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!
July 30th, 2011 4:51am

I have a column WEEK which is VARCHAR and has values like 201112,201135 in which last two characters represend the week no...so I need to create a derived column NEW_WEEK in such a way that if (cast( SUBSTRING( WEEK,5,2) as int)>=1 AND cast( SUBSTRING( WEEK,5,2) as int)<=17) Then NEW_WEEK=( cast( SUBSTRING( WEEK,5,2) as int)+17) ELSE (cast( SUBSTRING( WEEK,5,2) as int)-17) I am using following expression.. (((DT_I4) SUBSTRING( WEEK,5,2)>=1) && ((DT_I4) SUBSTRING( WEEK,5,2)<=17))?((DT_I4) SUBSTRING( WEEK,5,2)+35):((DT_I4) SUBSTRING( WEEK,5,2)-17)) Can somebody tell me whats wrong in it? I also tried.. IIF((DT_I4) [SUBSTRING( CALWEEK,5,2)]>=1 && (DT_I4) [SUBSTRING( CALWEEK,5,2)]<=17) then ((DT_I4) [SUBSTRING( CALWEEK,5,2)]+35) else ((DT_I4) [SUBSTRING( CALWEEK,5,2)]-17)) its also not working :( (DT_I4) SUBSTRING( WEEK,5,2) >=1 && (DT_I4) SUBSTRING( WEEK,5,2) <= 17 ? (DT_I4) (SUBSTRING( WEEK,5,2))+ 35 : (DT_I4) (SUBSTRING( WEEK,5,2))-17 I made a modification different from this expression which gave an error and now adjusted to the expression above. When i tired to paste this in Derived column expression, I am not able to do so due to weird behavior and I tried a lot but in vain, hope this expression resolves the issue. Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog! It still does not work.. :(
Free Windows Admin Tool Kit Click here and download it now
July 30th, 2011 5:08am

HI rockstar, This expression worked without any error (no red text), I copide in bits and pieces and it worked ? Do you get any error after the run?Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!
July 30th, 2011 6:02am

IT ACTUALLY DID WORK..THANKS YOU MATE..APPRECIATE YOUR HELP :)
Free Windows Admin Tool Kit Click here and download it now
August 27th, 2011 1:00pm

Great! Really happy to help!Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!
August 28th, 2011 3:35am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics