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 |
 |
| How to replace Null values in SSIS Package by using Derived Column Transformation |
- How to split single column into multiple columns in Data Flow Task in SSIS Package
- How to write IF Else statement in derived column Transformation in Data Flow task in SSIS Package
- How to convert Null values to Unknow in Data Flow Task in SSIS Package
- Convert Month Number into Month Name in Derived Column Transformation
- How to Load Unique Identifier values from Excel file to SQL Server Table
- Write Case Statement in Derived Column Transformation in SSIS Package
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