Null record drop warinig

[samsuf2002]

Hi All, I am running a parallel job which loads the data from 2 sequential file and load in SQL server using ODBC stage . I am using CDC , look up, sort , join stages in this job . I have a field Address2 (varchar 30 , nullable) which contain addresses and email addresses so i need map this field to EMAIL_ADDR field in my target table there fore i am using this logic--

IF count (DSLink231.Address2 ,'@') =1 THEN DSLink231.Address2 ELSE SetNull()

i am getting this warning and it is rejecting around 5 million recs out of 28 million.

APT_CombinedOperatorController(2),1: Field 'Address2' from input dataset '0' is NULL. Record dropped.

I have this field Nullable every where in my job

Any help will be appreciated

Thanks

[DSguru2B]

Can you double check at the database level, wheather this field really is nullable or not.

[samsuf2002]

It is Nullable in Database

[us1aslam1us]

Try something like this:

Code: Select all

If (IsNull(DSLink231.Address2) or count(DSLink231.Address2 ,'@')<>1) then SetNull() Else DSLink231.Address2

[balajisr]

You can also try this.

Code: Select all

IF count (NullToEmpty(DSLink231.Address2) ,'@') =1 THEN DSLink231.Address2 ELSE SetNull() 

[samsuf2002]

I used

if isnotnull(DSLink332.Address2) = 1 then ( IF Count(DSLink332.Address2, '@') = 1THEN DSLink332.Address2 ELSE SetNull()) else SetNull()

it worked fine.

Thanks to All

[us1aslam1us]

Balaji's logic seems to be much simpler and efficient.

[DS_MJ]

I am geeting the same error but for a timestamp field.

APT_CombinedOperatorController,1: Field 'UP_FOR_RENL_DT' from input dataset '0' is NULL. Record dropped.

COL_DATE is set to Nullable
COL_DATE is a Timestamp field.

This col on source side is a date col and on the target its a Timestamp col.

I convert the date col to timestamp col using the following:

Code: Select all

DateToString(Appointment_Data_lnk.ORIG_ISSUE_DT,"%yyyy-%mm-%dd"):' 00:00:00.000'

However, I get the above error. How do I do a check for nullable..?
What is wrong with this ?

Code: Select all

[quote]If IsNULL(DateToString(Appointment_Data_lnk.ORIG_ISSUE_DT,"%yyyy-%mm-%dd"):' 00:00:00.000') then SetNull() Else (DateToString(Appointment_Data_lnk.ORIG_ISSUE_DT,"%yyyy-%mm-%dd"):' 00:00:00.000')[/quote]

Thanks.

[DS_MJ]

Hello Problem resolved:

Code: Select all

If IsNULL(Appointment_Data_lnk.ORIG_ISSUE_DT then SetNull() Else DateToString(Appointment_Data_lnk.ORIG_ISSUE_DT,"%yyyy-%mm-%dd"):' 00:00:00.000'