SSIS: IF else in Derived Column expressions

Question

I am trying to convert the way time is written in the flat file source so it would actually look like time.(If that makes any sense).

Right now I have it as 1215, 630, 10, 1 ,.. etc. So it can be 4-1 character long, so I need to convert it depending on the length of the column.

I am doing this in Derived Column with an expression, however I can't seem to make it work, not sure if my syntax is wrong or what, the expression looks like this, however I'm getting errors:

(LEN([TIME OCC]) == 4) ? (SUBSTRING([TIME OCC],1,2) + ":" +  SUBSTRING([TIME OCC],3,2)) : (LEN([TIME OCC]) == 3) ? (SUBSTRING([TIME OCC],1,1) + ":" + SUBSTRING([TIME OCC],2,2)) : (LEN([TIME OCC]) == 2) ? (SUBSTRING([TIME OCC],1,2) + ":00") : (LEN([TIME OCC]) == 1) ? (SUBSTRING([TIME OCC],1,1) + ":00")

When I do it with only two values like below it seems to work perfectly:

LEN([TIME OCC]) == 3 ? SUBSTRING([TIME OCC],1,1) + ":" + SUBSTRING([TIME OCC],2,2) : SUBSTRING([TIME OCC],1,2) + ":" + SUBSTRING([TIME OCC],3,2)

I would appreciate any help, thank you!

"The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis" - I've tried a bunch of different ways of writing the expression and none of them seem to work. — zhirgis, Feb 20 '17 at 13:56
does `10` mean `00:10`? The simplest thing to do would be to left pad with zeros then split and put the `:` in. Then you don't need a great big nested expression — Nick.Mc, Feb 21 '17 at 05:46

score 4 · Answer 1 · answered Feb 21 '17 at 05:49

4

All these answers are too complicated. Way too complicated. To me this is far less complicated:

LEFT(RIGHT(("0000" + [TIME OCC]),4),2) + 
":" +
RIGHT(RIGHT(("0000" + [TIME OCC]),4),2)

answered Feb 21 '17 at 05:49

Nick.Mc

18,304
6
61
91

score 1 · Accepted Answer · answered Feb 20 '17 at 14:06

1

You are ending your expression with an incomplete ternary expression. I am adding some line breaks and indentation to make it more readable:

(LEN([TIME OCC]) == 4) ? 
(SUBSTRING([TIME OCC],1,2) + ":" +  SUBSTRING([TIME OCC],3,2)) : 
  (LEN([TIME OCC]) == 3) ? 
  (SUBSTRING([TIME OCC],1,1) + ":" + SUBSTRING([TIME OCC],2,2)) : 
    (LEN([TIME OCC]) == 2) ? 
    (SUBSTRING([TIME OCC],1,2) + ":00") : 
      (LEN([TIME OCC]) == 1) ? 
      (SUBSTRING([TIME OCC],1,1) + ":00")  <-- there needs to be a : with an ELSE condition here

I don't know if it's necessary, but I would also put a set of parenthesis around each of the nested ternary expressions.

answered Feb 20 '17 at 14:06

Tab Alleman

31,483
7
36
52

I tried making the last part into the ELSE condition and tried adding additional one still no luck. And parenthesis did not help either. Gonna try out anything, now. – zhirgis Feb 20 '17 at 14:22
Edit your post and add what you tried, so we can maybe spot the error. – Tab Alleman Feb 20 '17 at 14:26
Why pick the most complicated error prone solution? I guess I should get overit – Nick.Mc Feb 22 '17 at 04:54

score 0 · Answer 3 · answered Feb 20 '17 at 14:34

0

As Tab says, missing the final alternative, try this:

LEN([TIME OCC]) == 4 ? (DT_WSTR,2)SUBSTRING([TIME OCC],1,2) + ":" + (DT_WSTR,2)SUBSTRING([TIME OCC],3,2) 
: LEN([TIME OCC]) == 3 ?(DT_WSTR,1)SUBSTRING([TIME OCC],1,1) + ":" + (DT_WSTR,2)SUBSTRING([TIME OCC],2,2) 
: LEN([TIME OCC]) == 2 ? (DT_WSTR,2)SUBSTRING([TIME OCC],1,2) + ":00"  
: (DT_WSTR,1)SUBSTRING([TIME OCC],1,1) + ":00"

assuming the last alternative with a length 1.
I hope this help.

answered Feb 20 '17 at 14:34

ɐlǝx

1,384
2
17
22

I don't know if it was the type casts that I was missing, but this worked. Thank you! – zhirgis Feb 21 '17 at 07:17

score 0 · Answer 4 · edited May 23 '17 at 12:25

I don't think that Derrived column is the best way to solve your issue (it may be more complicated). you can add a Script component and mark your column as input, create a new output Column (Type DT_STR) and use the following code inside the OutpoutBuffer0_ProcessInputRow method:

Assuming that TIMEOCC and outColumn are your input and output column

If Not row.TIMEOCC_IsNull AndAslo _
   Not String.IsnullorEmpty(Row.TIMEOCC.trim) Then

   Select Case Row.TIMEOCC.Trim.Length

   Case 1

         Row.OutColumn = Row.TIMEOCC & ":00"
   Case 2

         Row.OutColumn = Row.TIMEOCC & ":00"
   Case 3

         Row.OutColumn = Row.TIMEOCC.Substring(0,1) & ":" & Row.TIMEOCC.Substring(1,2)
   Case 4

         Row.OutColumn = Row.TIMEOCC.Substring(0,2) & ":" & Row.TIMEOCC.Substring(2,2)
   Case Else

          Row.OutColumn = "00:00"
   End Select

Else

    Row.OutColumn = "00:00"

End If

If you want your solution by derrived column only

i think this is what you are looking for:

     ISNULL([TIME OCC]) ? "00:00" 
: (LEN([TIME OCC]) == 4 ? ((DT_WSTR,2)SUBSTRING([TIME OCC],1,2) + ":" + (DT_WSTR,2)SUBSTRING([TIME OCC],3,2)) 
: (LEN([TIME OCC]) == 3 ?((DT_WSTR,2)SUBSTRING([TIME OCC],1,1) + ":" + (DT_WSTR,2)SUBSTRING([TIME OCC],2,2))
: (LEN([TIME OCC]) == 2 ? ((DT_WSTR,2)SUBSTRING([TIME OCC],1,2) + ":00")  
: (LEN([TIME OCC]) == 1 ? ((DT_WSTR,2)SUBSTRING([TIME OCC],1,1) + ":00")
: "00:00"))))

References

SSIS: IF else in Derived Column expressions

4 Answers4