Command text was not set for the command object

I am using  a regular expression for a package variable and using the variable as a source to get the data in data flow task..but the expression seems to be wrong as its giving me the error: "Command text was not set for the command object"

The regular expression is:

"SELECT DISTINCT
pat_id,
TO_CHAR(pat_uid) AS pat_uid,
hmo,
member_number,
CONCAT(substr(date_of_birth, 1, 2), CONCAT(substr(date_of_birth, 4, 2), substr(date_of_birth, 7, 4))) AS date_of_birth, last_name,
CONCAT(CONCAT(first_name, ' '), middle_initial) AS first_name,
CASE
WHEN REGION_HMOA = 'EAS' THEN '94294'
WHEN REGION_HMOA = 'SF' THEN '94294499'
WHEN REGION_HMOA = 'SOL' THEN '94294696'
WHEN REGION_HMOA = 'SAC' THEN '94294497'
ELSE '94294489699'
END AS Medical_Group_id,
CASE
WHEN hmo = 'BS' THEN '002'
WHEN hmo = 'WH' THEN '027'
WHEN hmo = 'CC' THEN '016'
WHEN hmo = 'CG' THEN '022'
WHEN hmo = 'PC' THEN '012'
WHEN hmo = 'AE' THEN '020'
WHEN hmo = 'HN' THEN '007'
WHEN hmo = 'HA' THEN '027'
WHEN hmo = 'SN' THEN '018'
END AS health_plan_id
FROM XYZ_TABLE where HMO = '"+ @[User::Health_Plan_ID] + "' ORDER BY pat_uid"

 Can somebody please tell me whats wrong with it..Thanks in advance..

Are you using SQL Server as a data source or are you using a different SQL Engine as the source for this query?

** If ** it's SQL Server:

The issue is probably that there is no "TO_CHAR()" function in T-SQL.  Nor is there a "substr" function.   In their places, you will need to use "CONVERT( VARCHAR( {width} ) , pat_uid )" and "SUBSTRING" respectively.  The resulting variable would contain an invalid SQL clause as far as SQL Server is concerned.

If you're using ORACLE:

As a data source, make sure that you're using the "ORACLE for OLE DB" driver for your data source connection and NOT the Microsoft Oracle driver. 

The other possibility that comes immediately to mind is that SSIS might be trying to validate the query results from the variable - but before the value in the where clause is set.  You MIGHT try ( on the OLE DB Source ) setting the "ValidateExternalMetaData" to [False] if the first option doesn't help.

Hope it helps,

Keith Hyer

Thank you for the reply mate..the problem was EvaluateAsExpression was not set to true..its working now..thanks again for the help :)