SSIS -How To Use Derived Column Transformation (Replace Null Values) in SSIS Package

Scenario:

Let’s say we are extracting data from flat file or from Database table. Few of the columns have Null values. In our destination we do not want to insert these values as NULL. We want to replace Null values with “Unknown” for string type columns and 0 ( Zero) for integer type columns.

Solution:

If we are extracting data from Database table then we can use IsNULL function and replace with required values but If we are extracting data from flat file then we cannot use this function and we have to use some transformation in SSIS to perform this operation.
In Derived Column Transformation, there are different types of functions and operators are available those can help us to achieve this required results

  • Mathematical Function
  • String Functions
  • Date/Time Functions
  • Null Functions
  • Type Casts Functions
  • Operators

Here are the steps how we will be replacing Null values by using Derived column Transformation

Step 1:
Create connection to your flat file by using Flat File Source. I have used below data in text file for this example.Notice SaleAmount for Italy,andy is missing and SalePerson is missing for Brazil,,200

CountryName,SalePersonName,SaleAmount
uSA,aamir shahzad,100
Italy,andy,
UsA,Mike,500
brazil,Sara,1000
INdia,Neha,200
Brazil,,200
Mexico,Anthony,500

Step 2:
Bring Derived Column Transformation in Data Flow Pane and then connect Flat File Source to it. By double clicking derived column transformation, write expressions as shown below for SSIS 2008 and SSIS 2012 and latest versions.

SSIS 2012 introduced new function REPLACENULL that we can use and get the required results but in previous versions of SSIS, we have to use if else expressions with ISNULL Function to achieve this.
How you will read this expression
ISNULL(SalePersonName) ? "Unknown" : SalePersonName

 IF (?) SalePersonName is Null then “Unknown” Else (:) Column Value itself( SalePersonName)

SSIS 2008 Derived Column Expression
How to replace Null values by using Derived Column Transformation in SSIS Package


For SSIS 2012 and latest versions
How to use ReplaceNull function in Derived Column Transformation to replace Null in SSIS Package



Final Output
As we can see in snapshot the Null values are replaced by "Unknown" for SalePersonName and with 0 for SaleAmount
How to replace Null values in SSIS Package by using Derived Column Transformation

10 comments:

  1. The complexity of SSIS Insert operations is already very high and hence I think proper implication of products and packages is also very necessary.

    ReplyDelete
  2. Wow, amazing blog layout! How long have you been blogging for? you make blogging look easy. The overall look of your website is fantastic, let alone the content!

    3D animation Company
    Chatbot Development Company
    Android app development in Coimbatore

    ReplyDelete
  3. Hi
    How to pass Column_Name in (Value 1,Value 2 ..n) in dervied column in ssis package

    ReplyDelete
  4. With the help of creative designing team TSS advertising company provides different branding and marketing strategies in advertising industry...

    https://www.tss-adv.com/branding-and-marketing

    ReplyDelete
  5. Most of us love to eat chocolate. I really like to eat chocolates while studying. I prefer Belgium Chocolates when I crave for chocolates.

    ReplyDelete
  6. The games and the referral program through Jaa Lifestyle are one simple way of earning money online. Individuals can earn extra bucks by just spending their time on Jaa Lifestyle website and get actually involves in the flow. As well there are few option such as to watch the ads, refer friends and use other options from the website which also does earn money. Jaa Lifestyle India Login There are various ways Jaa Lifestyle provides money to individuals by just watching or using the services from it.Individuals to access Jaa Lifestyle website, they need to have got themselves register with the website through the official process. Here are the steps that you can follow to get access to Jaa Lifestyle Login ID.

    ReplyDelete
  7. I loved the information given above. Ziyyara Edutech’s CBSE home tuition services bring qualified and experienced tutors right to your doorstep through online sessions.
    For more info contact +91-9654271931 or visit CBSE home tuition

    ReplyDelete