Unable to reproduce success of StackOverflow article in transfering NULL value from file to a table

Question

I am running into a problem trying to repeat the success of this StackOverflow article to trasfer a NULL value in a CSV file to a table column that allows NULLs.

Given this CSV file with Text Delimiter = double Quote and Column Separator Character = Comma. Note that there are two records and the 3rd and 4th columns from the end, "CELL_TXT" and "CELL_VAL", respectively. The first column, "CELL_TXT",contains a text value. If this text value can be evaluated to a numeric, then CELL_VAL will contain this numeric value, otherwise NULL.

"FLDR","FILE_NM","TAB_IDX","TAB_NM","TAB_PART","LABEL_DESGTR","LABEL_TXT","SECT_NM","SEG_NM","COLMN_TXT","ROW_IDX","COLMN_IDX","COLMN_LETTR","CELL_TXT","CELL_VAL","LAST_OPER_ID","LAST_TIMESTMP"
"C:\corp_mlr_rebates\Processing\HHS\CGLIC","MLR_Template_CGLIC_Grand_Total.xls","1","Pt 1 and 2","Part 1","1.","Premium","Health Insurance Coverage","Individual","Total as of 12/31/11","19","6","F","2",2,"HHSSWEEP","1/15/2013 1:40:20 PM"
"C:\corp_mlr_rebates\Processing\HHS\CGLIC","MLR_Template_CGLIC_Grand_Total.xls","1","Pt 1 and 2","Part 1","1.","Premium","Health Insurance Coverage","Individual","3/31/12","19","7","G","",,"HHSSWEEP","1/15/2013 1:40:20 PM"

Note that for the second row, CELL_TEXT was blank, "", and so the CELL_VAL was stored as NULL in the CSV file, indicated by a consecutive comma column separator character immediately after the previous column separator.

I am trying o import this file into a table that allows NULL in the CELL_VAL table. Here's the full table, though we are interested in the CELL_TXT and in particular, CELL_VAL column.

CREATE TABLE [dbo].[HHS_GRD_STG](
    [FLDR] [varchar](255) NOT NULL,
    [FILE_NM] [varchar](80) NOT NULL,
    [TAB_IDX] [int] NOT NULL,
    [TAB_NM] [varchar](80) NOT NULL,
    [TAB_PART] [varchar](80) NOT NULL,
    [LABEL_DESGTR] [varchar](80) NOT NULL,
    [LABEL_TXT] [varchar](255) NOT NULL,
    [SECT_NM] [varchar](80) NOT NULL,
    [SEG_NM] [varchar](80) NOT NULL,
    [COLMN_TXT] [varchar](80) NOT NULL,
    [ROW_IDX] [int] NOT NULL,
    [COLMN_IDX] [int] NOT NULL,
    [COLMN_LETTR] [char](2) NOT NULL,
    [CELL_TXT] [varchar](255) NOT NULL,
    [CELL_VAL] [decimal](14, 4) NULL,
    [LAST_OPER_ID] [char](8) NOT NULL,
    [LAST_TIMESTMP] [datetime] NOT NULL
) ON [PRIMARY]

In SSIS, I define my Input File Object as a Comma Delimeted file with a " Text Qualifier and {CR}{LF} as the row Delimeter.

In the Data Flow Task, I set the "Retain Null Values" to true in the FIle Source object. In the destination Table object, I also set "the "Keep Nulls" property, but when I run the package, I get the following error.

Error: 0xC0202009 at Load HHS Child tables, HHS_GRD_STG Table [579]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Unspecified error".
Error: 0xC020901C at Load HHS Child tables, HHS_GRD_STG Table [579]: There was an error with input column "CELL_TXT" (659) on input "OLE DB Destination Input" (592). The column status returned was: "The value violated the integrity constraints for the column.".
Error: 0xC0209029 at Load HHS Child tables, HHS_GRD_STG Table [579]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "OLE DB Destination Input" (592)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (592)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at Load HHS Child tables, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "HHS_GRD_STG Table" (579) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (592). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

I noted that the first record of the file was successfully transferred to the dest table. This was the record witha NON NULL value.

Since the Destination table allows nulls for the CELL_VAL column, why is this task failing?

[CELL_VAL] [decimal](14, 4) NULL,

Follow up question:

William is correct in noting that there issue is due to another column containing a null value when the column does not allow NULLs.

This is because my package doesn't seem to differentiate between an empty string value

,"",

and a NULL value

,,

I specified that my file uses a double quote as a Text Delimiter for this purpose, but it doesn't seem to help. As a result, since the setting to "Retain NULLS' seems to be a file-levl property, I am forced to change the DDL of several columns to "Allow Null" when what I really want is for the other columns to be set to a zero length string when I have a value that is denoted by two consecutive text delimeters.

Is it possible to get SSIS to differentiate between a NULL andzero-length string value?

Answer 1

It appears to me that the error that you are showing is not about column CELL_VAL but about CELL_TXT.

There was an error with input column "CELL_TXT" (659) on input "OLE DB Destination
Input" (592). The column status returned was: "The value violated the integrity 
constraints for the column.".

This would seem to indicate that there is a foreign key relationship or some other constraint on column CELL_TXT and that as such a blank value is not allowed.

It has nothing to do with the null value in CELL_VAL.