DSXchange

I have a scinario where I want to convert string to date.
the problem is if I get null value in string it is droping that record.
If I take a variable port date datatype, and converting all notnull strings to date and else taking variable port. At that time it is outputting 01/01/2001 in null's position.

My Target table is oracle.

And I need Null string to load as Null date only.

Welcome aboard.

Read the section about null handling in the Parallel Job Developer's Guide - in particular the difference between in-band and out-of-band nulls. This should make it clear what you need to do.

Hi ray
I have gone through the null handling in the Parallel Job Developer's Guide and had a look into those functions but still i had the problem of passing the hardcoded null to date field.

If my question is wrong plz rectify me...

If you are using a Transformer stage specify SetNull() as the column derivation where you need null. If you are using a Modify stage use make_null() function. You will have to figure out what an out-of-band null for a date field looks like if you use the Modify stage.

Yes ray
following is my code in the transformation
If IsNotNull(VALUE_DATE) Then StringToDate(VALUE_DATE,"%yyyy%mm%dd"):'-00.00.00.000000' Else
SetNull()

this is working fine if the target is a dataset
where as my target is relational here it is droping the null records..

Instead of passing the records directly to table place a seq.file take the data to file and then from seq.file pass an output link to target table to insert records.

This may solve your problem.

mallick after trying your method
Still having the same problem droping the null records.
These are the messages that I am getting when ever a null is trying to load in Date format field

1)Oracle_Enterprise_17,0: The system(sqlldr test_abb/test_abb@EFDWDVY CONTROL=ora.594072.450364.0.ctl LOG=ora.594072.450364.0.log BAD=ora.594072.450364.0.log.bad SILENT=header PARFILE=ora.594072.450364.0.par) failed; see the log file for the Oracle specific message.
2)Oracle_Enterprise_17,0: The runLocally() of the operator failed.
3)Oracle_Enterprise_17,0: Operator terminated abnormally: runLocally did not return APT_StatusOk
4)main_program: Step execution finished with status = FAILED.

Is that particular column nullable in the table?
Also look at the .bad file for oracle error message.