In SQL Server, the CASE statement is a powerful tool that allows you to perform conditional logic within your queries. Whether you are working with SSIS or Azure Data Factory (ADF), the CASE statement can be used to achieve the same goal.

Problem Statement

Let’s consider a simple scenario where we want to return a value of 1 if the PortfolioTypeCode has a value of either ‘Mutual Fund’ or ‘Pooled Fund’, and 0 otherwise.

In SSIS

In SSIS, you can achieve this by using a T-SQL command within the OLEDB source component. Here’s an example:

SELECT Col1,
       CASE WHEN PortfolioCode IN ('Mutual fund', 'Pooled fund')
            THEN 1
            ELSE 0
       END AS IsFund,
       Col2
FROM Table1

In ADF

In ADF, you can achieve the same goal by using expressions. After establishing the source dataset in the data flow, you can add a ‘Derived Column’ activity and define the expression. Here’s an example:

case(PortfolioTypeCode == 'Mutual Fund', 1,
     PortfolioTypeCode == 'Pooled Fund', 1,
     0) as IsFund

The case expression in ADF takes three arguments: the condition, the true expression, and the false expression. If the condition is true, it will return the true expression; otherwise, it will return the false expression.

It’s important to note that while SSIS uses a SQL engine, ADF data flow uses a Spark engine behind the scenes. This means that the syntax and capabilities of the expressions in ADF are different from traditional T-SQL.

For more details on the case expression in ADF, you can refer to the Microsoft documentation.

Overall, the CASE statement is a versatile tool that can be used in both SSIS and ADF to perform conditional logic within your queries. Whether you are working with SQL Server or Azure Data Factory, understanding how to use the CASE statement can greatly enhance your data processing capabilities.