Analytics

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

tiethnic colleagues sitting at desk looking at laptop computer in office. stock photo

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.

Download

 

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.

Ssis Source

Step 2-

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

 

Home Ssis

 

Step 3-

Drag and drop one data flow

 

Data Flow

 

 

Step 4-

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

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.

 

NewBrouse

TableOk

 

Step 6-

Drag and drop OLE DB Destination and configure.

Ok

 

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.

Db3Db2

 

 

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.

Db5

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.

Edit

Db7

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.

Db8

 

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

Using SSIS Package.

Happy Reading!

 

 

 

 

Thoughts on “How to Convert Blank Values to Null in Flat File Source in SSIS Package”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Shobhit Bisen

Shobhit Bisen works at Perficient as an associate technical consultant. He has a firm grasp of SSIS, SSRS, Python, and SQL. Shobhit also has a good knowledge of Snowflake and ADF. He is always ready to learn new things.

More from this Author

Subscribe to the Weekly Blog Digest:

Sign Up
Follow Us
TwitterLinkedinFacebookYoutubeInstagram