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
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”.
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.
