Load NULL in the DB when we are getting the blank value for the columns in the CSV file

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

 Setting Retain Null values from the source as null values in the data flow

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)

 Mapping the column with the destination table and running the package.

Package executed successfully.    

See the records in the table   

Get the expected result.