SSIS Derived Column translated from SSMS query

Question

New to SSIS, been dealing with SSMS mostly. Anyone can help translating the below SSMS statement into SSIS Derived Column Transformation? Many thanks.

ReliabilityFactorInput  =  Case 
When (isnull(pn.LBOXMATL, 'OTHER') = 'OTHER' AND  (round(ISNull(edd.cal_year, eqd.YearManuf) + 1, -4)/10000<=2003) OR pn.LBOXMATL ='Cast Iron') AND (ceiling((pn.NOWAYS+1)/2)*2 >= 4) then '1.3'
When (isnull(pn.LBOXMATL, 'OTHER') = 'OTHER' AND  (round(ISNull(edd.cal_year, eqd.YearManuf) + 1, -4)/10000<=2003) OR pn.LBOXMATL = 'Cast Iron') AND  (ceiling((pn.NOWAYS+1)/2)*2 < 4) then '1.1'   
else '' 
End

I suggest you look here [link[ http://stackoverflow.com/questions/14487327/ssis-derived-column-if-then-else — MiguelH, Oct 31 '16 at 16:54

score 0 · Answer 1 · answered Nov 02 '16 at 18:26

1.Name a variable with whatever name you want with int data type

2.Use execute sql task

3.copy all the complete query into that task and specify the result Set to single row

4.Switch to Result Set page, choose the variable you create, and set the result name to 0

5.Now every time you run the package the variable will be assigned either 1.3 or 1.1

That variable could be used in Derived Column transformation in data flow now

SSIS Derived Column translated from SSMS query

1 Answers1