When we are working with the CSV file we may get the data for some columns as blank. Our requirement is that we need to load DB null for this value and for the date we need to set the max data like ‘9999-12-31’
We want to load the null value on the highlighted column.
Below is the table.
Now we are taking the data flow task and taking source as flat file and destination as OLEDB.Now running the package.
See the records.
For that column, it stores the black value. But our the requirement is that we need to store DB null.
There are below ways to overcome this issue.
- Setting Retain Null values from the source as null values in the data flow
- Using Derived column
We need to check this option in the flat file. See below.
Now running this package.
Suppose we have a date column and in this one, we are getting
the blank value in this case we can store the null value.
See the blow.File data
Records in db
Suppose we have a requirement like if we are getting the data
column as blank we need to set the max data like ‘9999-12-31’
Using the above approach we will not achieve the same.
Using Derived column
We can use the Derived column to check the data and if we get
the blank value for the column we need to replace it with NULL vale and for the
date column, we will replace it with the max date value.
we need to uncheck the below option.
Let’s see the blow
Below are the expressions.
DC_DOB: BirthDate == "" ? (DT_DBDATE)"9999-12-31" :
(DT_DBDATE)BirthDate
DC_Fnm : (DT_STR,25,1252)(TRIM(FirstName) == "" ?
(DT_STR,25,1252)NULL(DT_STR,25,1252) : FirstName)
DC_Lnm : (DT_STR,30,1252)(TRIM(LastName) == "" ?
(DT_STR,30,1252)NULL(DT_STR,30,1252) : LastName)
Package executed successfully.
See the records in the table
Get the expected result.
