SSIS Derived Column Expression to display blank based on the value of another field in a separate OLE DB Source

Question

I have two different OLE DB Sources from different servers merging into a merge join. I am thinking that I need to use a derived column to put the value of one field in one of the OLE DB Sources as empty based on the value of a field in the other OLE DB Source. For example- OLE DB SOURCE 1 is the demographics source which includes the address fields ( street address, city, state, zip) OLE DB SOURCE 2 has the field with the indicator on whether to send mail or not, if this field has an 'N' then do not send mail. So I need my output file to not display the address if there is an 'N' in the do_not_send_mail in the OLE DB SOURCE 2. I was thinking that a Derived Column is the best way to do this after the Merge Join but not sure on how to do the expression.

I have not tried the derived column yet because I am not sure if this is the best way to accomplish this.

My expected results are for the address information to not display in the file if there is an 'N' in the do_not_mail column of the OLE DB SOURCE 2.

Answer 1

You need to add a Derived column that replaces the original address column and use the conditional expression as below (one dervied column for each column):

Assuming that all your column datatypes are DT_WSTR and length = 50

street address

[do_not_send_mail] == "N" ? NULL(DT_WSTR,50) : [street address]

city

[do_not_send_mail] == "N" ? NULL(DT_WSTR,50) : [city]

state

[do_not_send_mail] == "N" ? NULL(DT_WSTR,50) : [state]

zip

[do_not_send_mail] == "N" ? NULL(DT_WSTR,50) : [zip]

Additional Information

• Derived Column Transformation
• ? : (Conditional) (SSIS Expression)
• NULL (SSIS Expression)