How to handle Case statement in Azure Data Factory (ADF) compare to SSIS?

This post will describe how do you use CASE WHEN statement in Azure data factory(ADF). If you are coming from SSIS background, you know a piece of SQL statement will do the task. However let's see how you do it in SSIS and the very same thing can be achieved in ADF.

Problem statement:

For my simple scenario, In case PortfolioTypeCode is either 'Mutual Fund' or 'Pooled Fund' it should return 1 Else it should return 0.

 

How do you do in SSIS?

In SSIS, under data flow you will have OLEDB source like below fig 1:

Fig 1: SSIS OLEDB source

 

And open the OLEDB source and then Write SQL command like below and you are done:

SELECT Col1,

          CASE WHEN PortfolioCode IN('Mutual fund','Pooled fund')

            THEN 1

            ELSE 0

END  IsFund,

Col2

From Table1

Fig 2: CASE WHEN under SQL command in SSIS

 

How do you implement in ADF?

However in ADF, to achieve the same you need to use Expressions. ADF have very same concept of data flow like SSIS. In the data flow, after the source dataset is established you can add 'Derived Column' activity like below Fig 3:

Fig 3: Adding derive column under data flow

 

Now you can give a new column name and then add the expression (Fig 4):

Fig 4: Derived column expression

 

Let's see how Case expression works: it takes 3 arguments, those are condition, true and false. However, it can have alternating condition which describe in the figure 5:

Fig 5: Case in Expression

 

For my simple scenario, If PortfolioTypeCode is either 'Mutual Fund' or 'Pooled Fund' it should return 1 Else it should return 0.

Since you can't have CASE WHEN rather using case as Expression, the code will look like below:

  case( PortfolioTypeCode=='Mutual Fund',1,

       PortfolioTypeCode=='Pooled Fund',1,0)

 

Adding Active Directory users to Azure SQL databases

If you are working with Azure SQL Database you will find adding user a bit different than general SQL DB, especially; adding Active Directory users. Though it's easy and a few steps, off course if you know the steps. While I was working on it and went through different routes so thought about sharing the easy one which worked like Gem.

There are two authentication approach for Azure SQL DB and SQL Managed Instance.

1) SQL Authentication

2) Azure Active directory Authentication

Fig 1.0: Authentication type in Azure

SQL authentication is straight forward, while you are creating database in Azure, you must have to put SQL server credential which will become your Azure database credential if you don't create database credential separately. As long as you have administrative credential to login to the database then you create all necessary users. However, you can't create Active directory users in Azure by logging as SQL authenticate user.

For example, I have logged in with the SQL admin user and then tried to execute below query to add Active directory user:

CREATE USER [user1@domain.com] 

FROM EXTERNAL PROVIDER 

WITH DEFAULT_SCHEMA = dbo;  

  

--add user to role(s) for the particular database

ALTER ROLE dbmanager ADD MEMBER [user1@domain.com]; 

ALTER ROLE loginmanager ADD MEMBER [user1@domain.com]; 

However, found below error:

Fig 2.0: Error creating AD user

The error message is self explanatory: "Principal 'user1@domain.com' could not be created. Only connections established with Active Directory accounts can create other Active Directory users."

So, how to solve it? Or How to add the AD user to Azure SQL DB ?

Step 1: Please login to you azure portal and find out your SQL server resource and you will find 'Active directory Admin' left side under settings. Please click 'Active Directory Admin' to find out UI like below where click 'Set admin' , now your Active Directory user account become Admin to the SQL server. 

Fig 3.0:  Active Directory Admin

Step 2: Now either use SSMS or Azure data studio and login with Active directory authentication. Since my organization have Multi factor Authentication (MFA) enables so I have chosen Azure Active Directoty- Universal with MFA, but you can choose either 'Active directory - Integrated' or 'Active Directory - Password.'

Fig 4: Login with AD user

Step 3: And now you can run the query to add Active directory user and give permission as you want, though traditional database level roles like db_datareader, db_datawriter, db_ddladmin, etc. are the same in Azure database, but roles like sysadmin, serveradmin, etc. don’t exist in Azure SQL database. There are two admin roles, dbmanager (similar to dbcreator) that can create and drop databases, and loginmanager (similar to securityadmin) that can create new logins which you can see in the below code:

CREATE USER [user1@domain.com] 

FROM EXTERNAL PROVIDER 

WITH DEFAULT_SCHEMA = dbo;  

  

-- add user to role(s) for the particular database

ALTER ROLE dbmanager ADD MEMBER [user1@domain.com]; 

ALTER ROLE loginmanager ADD MEMBER [user1@domain.com]; 

As soon as you execute the above code the AD user got all the necessary access to perform activities.