OLE DB Source Editor producing Error Message "Command Text was not set for Command Object" when passing SLQ Comman by Variable
I need to acquire a date from my destination server to be used in querrying my source server. I am successfully acquiring the date using an EXECUTE SQL TASK within the SSIS component of Visual Studio. Within this task I am also creating the SQL statement needed to extract the data I need from the source server imbeded with the proper date information. I verified this by first passing it to another EXECUTE SQL TASK for execution. This did not result in any errors. I am currently using this statement in a .dtsx file -- reseting it mannually each day -- so I know it works.
I have come upon an issue when I attempt to pass this variable to an OLE DB SOURCE.
- I set the variable in an EXCUCUTE SQL TASKS
- I attempt to configure the variable for use in a OLE DB SOURCE with
- Data Access Mode = SQL Command from Variable
- Variable Name is set to User::sqlText
- ValidateExternalMetadata Property for OLE DB SOURCE set to False
- I also set DelayValidation for the DATA FLOW TASK to True.
With all this in place I am unable to save these changes in the OLE DB SOURCE Editor. I keep getting the error message :
TITLE: Microsoft Visual Studio
Error at Data Flow Task [Source - Query ]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Any adivce on how to overcome this issue would be GREATLY appreciated.
i suggest that you take the value returned from the execute sql task and use it in an another ssis variable to build the sql statement via an expression. this is a fool proof way to ensure that your sql statement is correct.
Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
Thanks for the timely response.
I passed the variable to another EXECUTE SQL TASK and attemtpted to execute it's content.
I will see if I can address the issues and get back to you with the result on EXECUTE TASK as well as the OLE DB SOURCE.
I have cleaned up the issues with the content of the variable. The next issue I am facing deals with successfully transferring this variable's content to the next Task.
I have been experimenting with two approaches and have not been able to get either to work.
- Approach 1 - Involved trying to pass the content of local variable @sqlText to User::sqlText. Here I specified the Result Set as NONE, and in the Parameter Mapping Table I identifed User::sqlText with a Direction of OUTPUT. I assigned the parameter name of 0, and in the SQL Statement field I place a ? ..... SET ? = " ..... "; but this syntax appears to be incorrect generating this error message when parsed.
- Apprach 2 - Involved trying to pass the content of a Table Colume as a SINGLE ROW Result Set. In this approach I CREATE a TABLE, INSERT the content of the Local Variable into the Table, and then do a SELECT sqlText FROM ...... On the Result Set page I specifiy the RESULT NAME as sqlText and the variable name as User::sqlText. When taking this approach the code parses correctly but I get this error message upn execution...
- An error occurrred while assigning a value to variable "sqlText". No result rowset is associated with the execution of this query. HOWEVER, when I remove the SELECT Statement and set the Result Set as NONE, the Create Table works and it contains the command string.
Any advice on a valid approach for passing this variable .... ultimately too an OLE DB Source task.... would be appreciated.