Dervied Column - Expression?

Hi:

I have a Dervied Column Component, in which there is a column called RecStatusCode. The criteria to give a value to that column is below in SQL:

(SELECT CASE CertParticipant
WHEN 'Y' THEN 'C'
WHEN NULL THEN 'A'
END
FROM [dbo].[SchoolCertRequest]
WHERE SchoolID = (SELECT SchoolCode FROM [WebStrategy].[LAF].[LoanApplication] WHERE LoanApplicationID = @.LoanApplicationID) and SchoolBranch = (SELECT BranchCode FROM [WebStrategy].[LAF].[LoanApplication] WHERE LoanApplicationID = @.LoanApplicationID) AND LoanType=(SELECT LoanApplicationTypeCode FROM [WebStrategy].[LAF].[LoanApplication] WHERE LoanApplicationID = @.LoanApplicationID))

My question is how can I use this SQL in that expression field, or is it possible to have a user variable, with the above sql being the criteria, and the value could be used in the derived column, by just using the variable in the expression. In either case, I hope you understand what I am trying to accomplish. The reason why its like this is because currently in my Data Flow Task, i Have a OLEDB source which returns 25+ column values, the source connects to this derived column item, and then finally the destination oledb where the mapping takes place. So basically I am trying to avoid MERGE JOIN, because i tried using it , and it complicated the whole thing much more then it really needs to be. Like the RecStatusCode column, i have 5 more columns which are dervied, and the values are coming from a SQL statement similar to above. I really need some help here, and I have not seen anything in reference to this in BOL or any articles online. Thanks in advance.

I think a combination of Replace and ISNull functions will do the trick.

http://msdn2.microsoft.com/en-us/library/ms141196.aspx

http://msdn2.microsoft.com/en-us/library/ms141184.aspx

|||Is there a way to put SQL queries in a user variable? I am just trying to figure out, whats the best way to do this? Can someone please advise. Thanks.|||Try doing that in an OLE DB command... You can't execute a SQL statement in a variable. You can store a SQL statement in a variable as it's just a string. You can even make that statement dynamic using variable expressions.|||

Please disregard my first answer; I thought you were asking how to translate the case statement into a SSIS expression.

Anyway; have you consider to include that query with the query in your source component; assuming all tables are in the same DB it would work just fine. In case the data is in different DB/Server; you could use a staging table to load the data on a common place and then have one single query.

Otherwise a second source component with a merge join may be an option.