none
OLE DB Source Editor producing Error Message "Command Text was not set for Command Object" when passing SLQ Comman by Variable

    Question

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

    1. I set the variable in an EXCUCUTE SQL TASKS
    2. 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 [1]]: 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.

    A newbie..

     

    Monday, March 16, 2009 6:00 AM

Answers

All replies

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

    hth

    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    • Marked as answer by Steve Noga Monday, March 16, 2009 1:56 PM
    • Unmarked as answer by Steve Noga Monday, March 16, 2009 1:57 PM
    • Proposed as answer by COZYROC Wednesday, March 18, 2009 1:29 PM
    Monday, March 16, 2009 6:10 AM
  • Thanks for the timely response. 

    I passed the variable to another EXECUTE SQL TASK and attemtpted to execute it's content. 

    Encoutered errors...!

    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.

    Thanks again!

     

    Monday, March 16, 2009 12:09 PM
  • 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.
    1. 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.
    2. 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.

     

     

    Monday, March 16, 2009 1:56 PM
  • I did some additional searching and found some GREAT posting's by Rafael Salas.    It told me what I needed to know.  

    • Marked as answer by Steve Noga Tuesday, March 17, 2009 5:56 AM
    Tuesday, March 17, 2009 5:56 AM