NULL value for empty field when importing flat file in SSIS

When importing data from text file you may need to apply NULL to the field where no values present (blank). Not doing so the package may fail.

Consider a scenario; you received a text file contains sales order details, it need to be loaded in to database for further reporting or analysis.

The format of CSV file:

---------------------------------------------------------------
OrderId, OrderDate, CustomerId, Qty, UnitPrice
---------------------------------------------------------------

The table definition is:

CREATE TABLE SalesOrderDetails
(
OrderId INT NOT NULL,
OrderDate DATE NOT NULL,
CustomerId INT NOT NULL,
Qty INT NULL,
UnitPrice DECIMAL(10,4) NULL
)

The below image shows how the data in text file, if you look at the Qty field of 3rd row, there is no value present. The corresponding column in the destination table is defined as INT. So, you can’t insert non integer value into Qty column. If you do so, you may end up with the following error message.

Error code: 0x80004005.
Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".

clip_image002[4]

So the blank field needs to be converted to NULL before inserting into table. You can do this by enabling “retain null values from the source as null values in the data flow” setting available in the Flat File Source Editor.

clip_image004[4]

1 comment:


  1. You have a wonderful blog. This is really worth to read and it helps a lot of people to learn new information about this deceased. Please keep on posting. Thank you.

    Sorn
    www.gofastek.com

    ReplyDelete