Convert Blank Space to Null in SSIS

  • Firstly create a text file as a source file which is a comma-separated file. In the text file, some records have blank.
  • In the SQL Server, create a new table for the destination.
NOTEPAD FILE

In SSIS First drag and drop the Data Flow Task component in the control flow.

DATA FLOW TASK

Inside the Data Flow Task, drag and drop the Flat File Source. Flat File Source read the blank space from the .txt file and converts it into NULL values.

By configuring the Flat File Source right-click on it and go to the Edit option.

FLAT FILE SOURCE

In connection manager, create a new Flat File Connection by clicking the new button.

CONNECTION MANAGER

When are you clicking a button new pop-up window is open. In the General tab, Browse the text file.

GENERAL TAB

After that check the column in the columns tab.

COLUMNS TAB

In Advanced Tab change the datatype of ID and CODE into a four-byte signed integer.

ADVANCED TAB

Also, check the Preview tab it shows the blank space of the file. Click on the ok button.

PREVIEW TAB

In the Connection Manager tab, Check the Retain null values. After that check the columns and click on the ok button.

Retain null values specifies whether zero-length columns are treated as NULL values.

RETAIN NULL VALUES

Drag and drop the ADO NET Destination for the destination and configure it.

ADO NET DESTINATION

Here specify the table connection by clicking New. After that select table into SQL Server.

CONNECTION MANAGER

Provide Server name and database name into SQL Server and click on ok.

CONNECTION OF SQL SERVER

Before click, the ok button checks the mappings.

MAPPING TAB

Execute the project. After executing result shown in the SQL Server table.

EXECUTE THE PROJECT

In the table blank space converted into the NULL values.

RESULT

If you are new to MSBI start with the following must-watch video: -

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store