SSIS表达式

Question

我有以下SQL代码

update ETL.TempRH 
set resourcer = IF(
               qualification IN (47,49,50,164,187,252,185,239,251),
                   "resourcer"  , type_Staff);

我需要把它转换成ssis表达式。我试过了但没成功。

(qualification = 47  || 
 qualification = 49  ||  
 qualification = 50  || 
 qualification = 164 || 
 qualification = 187 || 
 qualification = 252 || 
 qualification = 185 || 
 qualification = 239 || 
 qualification = 251 ? "resourcer" : type_Staff)

Answer 1

您遇到的一个问题是SSIS中的相等比较运算符是==而不是=。

更广泛地说，这些资历数字会不会发生变化？它们甚至是模模糊糊的动态吗？

您可能只想使用一个查找组件来存储您的资源限定值，根据它查找限定值，如果匹配，则将您的type_Staff列替换为" resourcer“。

因此，例如，在查找中，将查询设置为

SELECT 47 as Qualification, 1 as IsResourcer
union
SELECT 49, 1
etc
etc

加入资格，然后在派生列中只需

(IsResourcer == 1 ? "Resourcer" : Type_Staff)

如果您不关心保留原始type_Staff值，则可以将查找查询更改为

   SELECT 47 as Qualification, 'Resourcer' as type_Staff
    union
    SELECT 49, 'Resourcer'
    etc
    etc

并将type_Staff设置为替换现有的type_Staff列。

Answer 2

只需在SSIS表达式中使用以下嵌套查询逻辑：

Field == "TrueCondition1" ? “True” : ( Field == "TrueCondition2" ? “True” : ( Field == "TrueCondition3 " ? “True” : ( Field == "TrueCondition4" ? “True” : “False"  ) ) )

因此，根据查询字段==限定&您正在检查的限定值将成为"TrueCondition“。

@[User::qualification]  == 47 ? @[User::resourcer]  : (@[User::qualification] == 49 ? @[User::resourcer] : (@[User::qualification] == 40 ? @[User::resourcer] : @[User::type_staff] ))

尝尝这个。