how to set not null column to null

[developeretl]

Hi,
Iam trying to set not null column to be nullable. I dnt want to default it to the some null value. I want it to be blank. Can you please guide me how to set this in the transformer .....

[nani1974]

Hi,

NullToValue(ToTrns.Field," ")

[developeretl]

Hi,

NullToValue(ToTrns.Field," ")

Hi,
Thanks for the reply.....It is not null column and i want it to be null when there is no data ... does it work in transformer...

[kumar_s]

NullToEmpty() ??

[chulett]

It is not null column and i want it to be null when there is no data

This contradicts your original post, at least there you wanted to set it to a space which could be legal. There is no way to allow a null in a column that does not allow them.

[Sreenivasulu]

You can set this to some default value like 'NULL1' and the at the end of the all your stages convert it to its original value.

Regards
Sreeni

[ray.wurlod]

It is not null column and i want it to be null when there is no data

This won't work anywhere. If it's not null you simply can not set it to be truly null. That would break a rule, and the rule is enforced.

Choose an "in-band null" - a value (perhaps blank) that is valid for the data type of the column and that does not occur in the data.

[natashab]

If you want to set a Not Null field to a blank space then you can simply give " " in the transformer stage.It's a blank space.
Or if you want to set a blank space when the input column's data is Null/no data use the below derivation in the transformer stage :
If IsNull(inputcolumn) Then " " Else inputcolumn.

[developeretl]

If you want to set a Not Null field to a blank space then you can simply give " " in the transformer stage.It's a blank space.
Or if you want to set a blank space when the input column's data is Null/no data use the below derivation in the transformer stage :
If IsNull(inputcolumn) Then " " Else inputcolumn.

Thank you for your reply.
I got one more issue, Iam doing keep date field as null in the transformer stage and target is the sequencial file stage. Iam getting error " nullable field but no null handling properties", but this is working for the Dataset. The column i want to be null in target is nullable but iam not able to populate the field with null in the sequential stage but iam able to do with dataset stage ? can you please guide me

[kris007]

You cannot write NULL values into Sequential File. You need to set NullFieldValue = '' in the Sequential file stage format tab.

[developeretl]

You cannot write NULL values into Sequential File. You need to set NullFieldValue = '' in the Sequential file stage format tab.

I Kris i did exactly what others had suggested me . I kept " " as NULL file value in the sequential file stage. But i got this errors ( for 5 columns with different lenths)
When checking operator: When validating export schema: At field "col1": "null_field" length (1) must match field's fixed width (17)
When validating export schema: At field "col2": "null_field" length (1) must match field's fixed width (7)

can you suggest me ?

[ray.wurlod]

For Char data type the Null Field Value must have the correct number of characters for the field. This has been explained about a bazillion times here.

[kris007]

You will need to define null field values for each column separately.

[developeretl]

You will need to define null field values for each column separately.

Hi Kris,
For one column I have specified null field value but i have nearly 6 columns how do i specify them. Do i need to keep 6 null field values in the sequential stage, if so do they correspond exactly to those columns only or it might be to other columns also . How can I make sure that col1 null field value corresponds to col1 only. Similar way to col2 to col6 can you please tell me.

[chulett]

You add the appropriate "null field value" property to each column, individually.