Transforming a SQL Case Statement in SSIS

Question

I trying to create a SSIS package to unpivot a table. The unpivot works fine. However the problem I'm having is the original sql contains two case statements and I can't seem to get it to output the results to a derived column. Not sure if a derived column is the way to go or not.

Here is the original SQL:

    SELECT reading_no
      ,[time] as sample_date_stamp
      ,[type]
      ,[units]
      ,[Parameter] as analyte
      ,[value] as results
      ,case when substring([VALUE],1,1) not in ('<','') then cast(VALUE as decimal) end as result_fixed
      ,case when substring([VALUE],1,1) in ('<','') then 1 else 0 end as results_ltd
    FROM TEMP

Here is the sample data:

reading_no  sample_date_stamp           type    units   analyte results  result_fixed   result_LTD
2002        2022-04-04 12:10:00.0000000 Soil    ppm     AG      9.52     10             0
2003        2022-04-04 12:39:00.0000000 Soil    ppm     AG      NULL NULL               1
2004        2022-04-04 12:42:00.0000000 Soil    ppm     AG      < LOD     NULL          1
2005        2022-04-04 16:30:00.0000000 Test    ppm     AG      < LOD     NULL          1

I will need to use a Derived Column Transformation in the package somewhere to account for the case statements. Currently I have the DCT after I unpivot the table and try to use an expression to transform the Case statements. I used the following expressions for the case statements in my Derived Column:

FINDSTRING( [RESULTS] ,"<", 1)) == 0 ?  (DT_DECIMAL, [RESULTS])
FINDSTRING( [RESULTS] ,"<", 1)) != 0 ?  (DT_DECIMAL,"1") : (DT_DECIMAL,"0") 

Getting the following error:

Error at Data Flow Task 1 [Derived Column [1182]]: Attempt to parse the expression "FINDSTRING( [RESULTS] ,"<", 1) == 0 ? (DT_DECIMAL, [RESULTS])" failed. 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.

Thanks in advance..

Answer 1

I believe your issue in these expression

(DT_DECIMAL,"1") : (DT_DECIMAL,"0")

Don't wrap the Scale in a string, instead use but you'll still need to apply the casts to something

(DT_DECIMAL,1) : (DT_DECIMAL,0)

Oh, but you actually want the values 1 or 0 here so if you really want them as decimal, I'd be lazy and just use

1.0 : 0.0

or if you want the cast just to learn how to do it

(DT_DECIMAL,0)"1" : (DT_DECIMAL,0)"0"

Similarly, with the first expression, you need to give the cast a number of decimal places and the existing expression won't do it

FINDSTRING( [RESULTS] ,"<", 1)) == 0 ?  (DT_DECIMAL, 5) [RESULTS]

That would give a decimal with 5 degrees of precision