SSIS Load Flat File Blank spaces as SQL Server Nulls

This SSIS Integration Services article shows how to load blank or empty spaces in theft file data as the NULLs values to the SQL Server table with an example.

The below screenshot shows the records in the Employees Nulls text file. It has few empty or blank spaces.

Text File

SSIS Load Flat File Blank spaces as SQL Server Nulls

Drag and drop the Data Flow Task into the control flow region.

Add Data Flow Task

Double-click to open the SSIS Data Flow Region. Then, drag the flat file source and double-click on it to open the Source Editor. Next, click the New button to open the Flat File Connection Manager Editor window.

SSIS Load Flat File Blanks as NULLS in SQL 3

Click the Browse button to choose the Employee text file with a few Empty spaces.

Use Browse button to choose the text file

If the file’s first row has header names, checkmark the Column Names in the first data row option.

Checkmark the Column Names in the first data row

Within the Column tab, check the columns and rows and change the Column Delimiter (if needed). 

SSIS Load Flat File Blank spaces as SQL Server Nulls 6

Use the advanced section to change the Data type, as they have to match with the destination table; otherwise, it throws an error. 

Change the Column Data types

Next, checkmark the Retain null values from their source as the null values in the data flow option and click OK to close the window.

SSIS Load Flat File Blanks as NULLS in SQL Server 8

Drag the OLE DB Destination and double-click on it to open the Editor. Next, click the New button to configure the OLE DB Connection Manager. Here, we have chosen the existing one.

Use OLE DB Destination to Configure OLEDB Connection Manager

Select the Load Data with Nulls table from the list. If not, click the new button to create a new one.

Select the Table

Next, go to the Mappings tab to check the input and available destination column mapping.

Check the input and available destination column mapping

Run the SSIS Load Flat File Blank spaces as SQL Server Nulls package.

Execute the SSIS Load Flat File Blank spaces as SQL Server Nulls package

Open the SQL Management Studio to check whether NULLs replace blank spaces or values.

View SQL table with NULLS