REPLACENULL Function in SSIS

When we are loading the data from source to destination, one of the biggest challenges to handling the NULL value because if we do any operation on this value like string operation (like concatenate) or mathematical operation (add, minus etc.) will be null.

There are multiple way to handle the null value. In 2012 SSDT REPLACENUL function in SSIS.

Different ways to replace NULLvalue in sql server:

https://bageshkumarbagi-msbi.blogspot.com/2015/12/different-ways-to-replace-null-in-sql.html

For handling the null ISNULL function is also available but this is the Boolean function.

Syntax

REPLACENULL (expression 1, expression 2) 

So if expression1 is NULL, it will be replaced by expression2 by the derived column transformation. If it is not NULL, expression1 itself is returned. If both are NULL, NULL is returned. If the data types of both arguments are different, SSIS will try to convert the data type of the 2nd expression to that of the 1st expression. If the data types are incompatible, an error will be returned.

Let’s see the demo

I have a table

create table emp (ID int identity(1,1) primary key, E_name varchar(50) null, E_mobile varchar(10) null, E_add1 varchar(50) null, E_add2 varchar(50) null, E_salary int null)

Inserting son records in this table

insert emp (E_name) values ('Bagesh') insert emp (E_name,E_Mobile) values ('Rajesh','888880XXXX') insert emp (E_name,E_Mobile,E_add1) values ('Rohan','888880XXXX','Delhi') insert emp (E_name,E_Mobile,E_add1,E_add2) values ('Mohan','888880XXXX','Delhi','Noida') insert emp (E_name,E_Mobile,E_add1,E_add2,E_salary) values ('Rohit','888880XXXX','Delhi','Noida',20000) insert emp (E_name,E_Mobile,E_add2) values ('Mohan','888880XXXX','Noida')

See the records

                          

We have below business rules to load these data into the destination.

Rule 1: If E-mobile is null then set NA

Rule 2: If E_add1 is null then use the value of E_add2 and if both NULL then set default value Unavailable.

Rule 3: If the salary is null then set 0.

Now taking Data flow task (DFT) and source as the OLEDB and creating the configuration

 

Now I am taking the Drive column we need to write the below expression for the rules

Rule 1 : REPLACENULL(E_mobile,"NA")

Rule 2: (REPLACENULL(E_add1,"") == "" && REPLACENULL(E_add2,"") == "") ? "Unavailable" : REPLACENULL(E_add1,E_add2)

Rule 3: REPLACENULL(E_salary,0)

Now I am executing this package

                             

Get the expected result.

If both are NULL, NULL is returned. If the data types of both arguments are different, SSIS will try to convert the data type of the 2nd expression to that of the 1st expression. If the data types are incompatible, an error will be returned.

See below I am trying to assigning the salary value null to Zero , in this case it will throw an error. 

Now running this package.

Throw an error

See the error

               

Below are the error

[Derived Column [2]] Error: An error occurred while evaluating the function. [Derived Column [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Derived Column" failed because error code 0xC0049067 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[Drived_Col_Salary]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure. [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Derived Column" (2) failed with error code 0xC0209029 while processing input "Derived Column Input" (3). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.