I am trying to write a nested isnull expression combining data from multiple fileds into one column. Here is my problem Isnull((CASE WHEN parameter_name = 'DocumentID' THEN parameter_value ELSE NULL END), Isnull((CASE WHEN parameter_name = 'FileID' THEN parameter_value ELSE NULL END), (CASE WHEN parameter_name = 'docIds' THEN parameter_value ELSE NULL END))) AS documentid, I have more then three fields , i almost have about six fields. the approach i am trying to take is ISNULL(DocumentID) ? FileId : ISNULL(FileId) ? FieldID : ISNULL(FieldID) ? docids : FieldID if documentid is null then use Field ID if FieldID is null then use File ID if File id is null then use Docids if that is null then use FileID but apaprently it doesn't pass the one level Thoughts ?

Should be like this I think: ISNULL( @[User::DocumentID] )?(ISNULL( @[User::FileID])?@[User::DOCIDS]:@[User::FileID]):@[User::DocumentID] If it were me I'd use a script task - that expression is going to be a huge pain to write and it won't make sense to anyone who looks at it. Or if your source is SQL Server then just use a Coalesce(DocumentID,FileID,DocsID) Chuck Pedretti | Magenic North Region | magenic.com

Hi ns100, Please try CASE WHEN parameter_name = 'DocumentID' OR parameter_name = 'FileID' OR parameter_name = 'docIds' THEN parameter_value ELSE NULL END as documentid Remember to mark as an answer if this post has helped you.

Headsup with Script Task, it will be a row by row process, the best suggestion is by Chuck by using a "Coalesce(DocumentID,FileID,DocsID)"Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

THe problem is little different here. The table which i am extracing is from a Key value Pair Table and i am trying to load a dimension out of it. So Colasce won't work in sql by iteself. Currently I am dealing this in sql with case statment and i am able to derive the values to what i am looking for .