How to replace NULL with Not Available in SSIS?

Question

Hi,

I have my input data like below. I want to load the data after replacing NULL values with 'Not Available' in Manager column in SSIS Package.

Dept Table:

Id Dept_Name Manager

1 HR Kumar

2 IT NULL

3 FIN NULL

4 Admin Rahim

In SQL Server, we can use ISNULL or COALESCE to replace NULL. Can anyone let me know how we can in SSIS?

Thanks in Advance.

Answer 1

Hi @Ramana Kopparapu,

Please make sure the value is NULL value, if it is a string, please use below expression in SSIS.

REPLACE(Manager,"NULL","Not Available")

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi @Ramana Kopparapu,

You can use SSIS REPLACENULL() function for that.

You need to use SSIS Derived Column task with the following expression:

REPLACENULL([dept_manager], "Not Available")

For the reference: https://learn.microsoft.com/en-us/sql/integration-services/expressions/replacenull-ssis-expression?view=sql-server-ver16

Answer 3

Even I apply ISNULL in expression, I am not getting the output properly in destination tables.

Am I doing anything wrong?

Answer 4

In your data flow have you use columne named "Replace 'dept_manager'" instead of "dept_manager"?