How to Convert Blank Values to Null in Flat File Source in SSIS Package

SQL Server Integration Services(SSIS):

SQL Server Integration Services is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a data warehousing tool used for data extraction, transformation, and loading. In this blog we are going to learn how we convert blank values to null in flat file source.

 

Control flow:

Control flow is the SQL Server workflow engine that contains control flow elements. An SSIS package consists of at least one control flow task, and optionally one or more data flows.

Data Flow:

Data flow in SSIS defines the flow of data from a source to a destination. The Data Flow Task is the most frequently used task in SSIS, which contains the data transformation logic (ETL processes). It moves the data from source to destination and adds transforms in them to merge, update or split data.

Flat File:

Flat Files are text files that store tabular data and are manipulated line-by-line. To handle Flat Files, you must create an SSIS Flat File connection manager, where you define the Flat File metadata.

 

Now you understand about SSIS, Control Flow, Data Flow, Flat File in SSIS

Let’s see how we Convert Blank Values to Null in Flat File Source using SSIS.

Step 1-

Create one Flat File with Some Blank data.

Step 2-

Now open Visual Studio (SSDT) and create one project and inside that project create one new package.

 

 

Step 3-

Drag and drop one data flow

 

 

 

Step 4-

Click on Data Flow and Drag and Drop Flat File Source and right click on it then click on edit.

 

Step 5 –

 First, we select our Source File.

In General tab Click on New then browse your file from location .

In column tab you can preview your data sets.

In Advanced tab you can change datatype on the basis of columns.

If all the configuration is done you can preview your datasets and then click on OK.

 

 

Step 6-

Drag and drop OLE DB Destination and configure.

 

In connection Manager tab click on New to connect with your Local Server, Database, and Table.

Also, create one table in the SQL Server with same column and data types.

 

 

Step 7-

After all configurations run the package.

It will load data from flat file to table, but it will load blank data in string columns and zero in integer columns, but we want Null value instead of blank value.

Step 8-

Now we right click on flat file connection and select show advance editor,

Then select component properties and then configure True in the Retain Nulls.

Step 9-

Now we again run the package, after completion of package execution,

We can see table is updated with Null values in place of blank values.

 

Here We Successfully Convert Blank Values to Null in Flat File Source

Using SSIS Package.

Happy Reading!