Dec 3, 2019
Convert Blank Space to Null in SSIS
- In this article, we convert blank space into the NULL value in SSIS and the results shown in the SQL Server Table.
- 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.
In SSIS First drag and drop the Data Flow Task component in the control flow.
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.
In connection manager, create a new Flat File Connection by clicking the new button.
When are you clicking a button new pop-up window is open. In the General tab, Browse the text file.
After that check the column in the columns tab.
In Advanced Tab change the datatype of ID and CODE into a four-byte signed integer.
Also, check the Preview tab it shows the blank space of the file. Click on the ok button.
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.
Drag and drop the ADO NET Destination for the destination and configure it.
Here specify the table connection by clicking New. After that select table into SQL Server.
Provide Server name and database name into SQL Server and click on ok.
Before click, the ok button checks the mappings.
Execute the project. After executing result shown in the SQL Server table.
In the table blank space converted into the NULL values.
If you are new to MSBI start with the following must-watch video: -
