Command text was not set for the command object

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Command text was not set for the command object

Author

Topic

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2012-02-06 : 19:43:36

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..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-06 : 19:51:32

Show us the application code for this as that error indicates it's not a SQL problem but rather your query isn't being set properly in the code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2012-02-06 : 19:56:17

Here you go:

<DTS:Property DTS:Name="Expression">"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"</DTS:Property>
<DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property>
<DTS:Property DTS:Name="Namespace">User</DTS:Property>
<DTS:Property DTS:Name="ReadOnly">0</DTS:Property>
<DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property>
<DTS:Property DTS:Name="IncludeInDebugDump">2345</DTS:Property><DTS:VariableValue DTS:DataType="8"></DTS:VariableValue>
<DTS:Property DTS:Name="ObjectName">SourceOracleSQL</DTS:Property>
<DTS:Property DTS:Name="DTSID">{9EF47223-85B7-4B26-9BCB-20A2DFE73D11}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable>

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2012-02-06 : 19:57:04

Can the single quotes being used in the case statements create any problem?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-06 : 20:00:47

Sorry I didn't notice this was an SSIS question. This one is out of my league.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 20:23:44

the way to do this is to create a variable in ssis of type a string and set the EvaluateAsExpression property true for it. then in expression builder set the above expression. then in dataflowtask select sql command from variable option and map it to variable created in package.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2012-02-07 : 00:05:35

yes..you are absolutely right..I have followed the exact steps..but still the problem persists..I think there is something wrong with the REGEX i have written.. :(

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2012-02-07 : 13:13:52

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 13:49:23

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources