Share via

How to replace NULL with Not Available in SSIS?

Ramana Kopparapu 286 Reputation points
2024-07-07T15:23:51.32+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,496 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,547 questions
0 comments
Accepted answer
  1. ZoeHui-MSFT 35,871 Reputation points
    2024-07-08T02:00:59.2966667+00:00

    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.

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 25,746 Reputation points
    2024-07-07T15:36:16.8+00:00

    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

  2. Ramana Kopparapu 286 Reputation points
    2024-07-07T16:27:03.8+00:00

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

    Am I doing anything wrong?

    User's image

    User's image

    0 comments
  3. Olaf Helper 43,806 Reputation points
    2024-07-08T05:59:17.1966667+00:00

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

    0 comments