I have to convert several Oracle's CTL script files to a SQL Server 2014 format files. I have 90% of the conversion complete, with the exception of the date fields. Within the DAT file that we receive from our client, if a date value is not given, the value of "00000000" is specified (as oppose to leaving the field null).
When I try to do a BULK INSERT, I get an error message indicating "Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row #, column 11 (ADMSN_DT)". The cause of the error is the string value "00000000".
Is there a way of specifying the following Oracle statement in T-SQL of a format file?
ADMSN_DT DATE 'YYYYMMDD' NULLIF ADMSN_DT="00000000"
My format file has the field defined as data type "DATE". Is there a way of imbedding logic into the format file to null out the field if it reads the value "00000000" for a give date field (as done in Oracle)?