Specifying Null Character For Loading Tables Using Bulk Loader

Hi All,

my dba folks are coming back with an answer to a question that sounds strange and I thought I would check on here.



Situation.

We are using an ETL tool I developed to move data around and building a DW on 2005. In some cases we are using the bulk loader to load and in some cases the tool itself.

One of the defaults of the tool is to truncate trailing blanks....and so fields that contain only blanks get truncated to a zero length character string.

When reloading the data with the tool it carries a null indicator next to the field value so the zero length character string is loaded as not null.

When loading with the bulk loader the dbas are telling me that the field is translated to a null. Note that they want some fields translated to null so they are using the keepnulls parameter.

On other databases (and built into the tool because this is so) the bulk loaders usually allow the specification of the load statement at column level and the specification of a 'null character sting' to be translated to null if this string is found. I put an example below.

I seem to recall that SQL Server 7 had some sort of bulk loader that allowed specification of columns at column level......for example offsets or whatever and the specification of fields to be interpreted as nulls. (Though that was a long time ago.)

I have searched through the manual and I don't see an option there any more to specify a character that will be interpreted to a null by the bulk loader.

Is it possible in 2005 to specify a character such that when the bulk loader sees it the field will be set to null?? And not just set fields to null which are not present in the load file?

(Just by the way, we are going to make the truncate trailing blanks optional and it's easy....it's just that I thought this kind of null if option was available in 2005 and I am keen to know if it is not there, either gone or never was...)



Thanks in Advance and Best Regards



Peter





Example of how Oracle does it...on page

http://www.csee.umbc.edu/help/oracle8/server.815/a67792/ch05.htm#5754
NULLIF Keyword
Use the NULLIF keyword after the datatype and optional delimiter specification, followed by a condition. The condition has the same format as that specified for a WHEN clause. The column's value is set to null if the condition is true. Otherwise, the value remains unchanged. NULLIF field_condition


The NULLIF clause may refer to the column that contains it, as in the following example: COLUMN1 POSITION(11:17) CHAR NULLIF (COLUMN1 = "unknown")