SSIS: IF else in Derived Column expressions

2
votes

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!

4
sql-serverssisexpressionetlderived-column
What error message are you getting? - Tab Alleman
"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. - Žygimantas Katkevičius
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.McDermaid

4 Answers

3
votes

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)
1
votes

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.

0
votes

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.

0
votes

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