Q:

How to check each row in a table for at least one null or constant value in SSIS?

I have a table with 20 fields.
1) Is there any way to check all fields if any of them are NULL?
2) For all columns that are nvarchar i want to search if they contain a constant string value e.g. "Missing".
Can i do the above in SSIS without having to check the columns one by one?

A:

There is no Null-inator that would check if any column is null in a row.  However, you could concatenate all the columns in the row and if any column is null then the result will be null.  So in a derived column task it could look like this:
Field1 + Field2 + (DT_WSTR, 50)Field3...

As demonstrated in field3, all fields that are not string would need to converted.
Similarly, you can find a keyword, like "missing" by concatenating all the fields in a derived column task and using FINDSTRING().  That could looks like this:
FINDSTRING(Field1 + Field2, "Missing",1)

If the value is greater than 0, you have a hit.  There a drawbacks though.  Given that your columns are nullable, you would need null handling on all the columns unless this test was only performed on rows where there are no nulls.  Also, it doesn't tell you which column has the value, so that probably is not very useful unless you are rejecting or quarantining the entire row.  

