none
Using a Variable in SSIS - Error - "Command text was not set for the command object.".

    Question

  • Hi All,

    i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,

    enterName - String packageLevel (will store the name I enter)

    myVar - String packageLevel. (to store the query)

    I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName =  " + @[User::enterName] + " )"

    Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error. 

     Error at Data Flow Task [OLE DB Source [1]]: 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.".

    Can Someone guide me whr am going wrong?

    myVar variable, i have set the ExecuteAsExpression  Property to true too.

    Please let me know where am going wrong?

    Thanks in advance.

     

     


     

     

    Saturday, November 04, 2006 3:25 PM

Answers

  •  MShetty wrote:

    Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error. 

      

    In addition to Jamie comment; In the OLEDB source component you should use myvar variable instead enterName.

    BTW, make sure to use 'evaluate expression' in the query builder to check your SQL statement is right.

    Rafael Salas

    Saturday, November 04, 2006 8:34 PM
    Moderator
  •  MShetty wrote:

    Hi All,

    i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,

    enterName - String packageLevel (will store the name I enter)

    myVar - String packageLevel. (to store the query)

    I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName =  " + @[User::enterName] + " )"

    Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error. 

     Error at Data Flow Task [OLE DB Source [1]]: 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.".

    Can Someone guide me whr am going wrong?

    myVar variable, i have set the ExecuteAsExpression  Property to true too.

    Please let me know where am going wrong?

    Thanks in advance.

     

     


     

     

    This *might* be a red herring because the result of that expression will not result in a valid SQL statement. You've missed the apostrophes out. Try this instead:

    "Select * from db.Users where (UsrName =  '" + @[User::enterName] + "' )"

     

    -Jamie

    Saturday, November 04, 2006 5:36 PM
    Moderator

All replies

  •  MShetty wrote:

    Hi All,

    i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,

    enterName - String packageLevel (will store the name I enter)

    myVar - String packageLevel. (to store the query)

    I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName =  " + @[User::enterName] + " )"

    Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error. 

     Error at Data Flow Task [OLE DB Source [1]]: 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.".

    Can Someone guide me whr am going wrong?

    myVar variable, i have set the ExecuteAsExpression  Property to true too.

    Please let me know where am going wrong?

    Thanks in advance.

     

     


     

     

    This *might* be a red herring because the result of that expression will not result in a valid SQL statement. You've missed the apostrophes out. Try this instead:

    "Select * from db.Users where (UsrName =  '" + @[User::enterName] + "' )"

     

    -Jamie

    Saturday, November 04, 2006 5:36 PM
    Moderator
  •  MShetty wrote:

    Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error. 

      

    In addition to Jamie comment; In the OLEDB source component you should use myvar variable instead enterName.

    BTW, make sure to use 'evaluate expression' in the query builder to check your SQL statement is right.

    Rafael Salas

    Saturday, November 04, 2006 8:34 PM
    Moderator
  • Thx Rafael, Jamie.. It worked.....
    Sunday, November 12, 2006 12:31 PM
  • MShetty,

    Can you please tell me how did you solve it ?
    I am getting the same error while i try to pass SQL satement as Variable in OLE DB Source.

    TITLE: Microsoft Visual Studio
    ------------------------------
    Error at DFTask_Sales_Fact [OLE DB Source Chd Query [1]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E0C.
    An OLE DB record is available.  Source: "OLE DB Provider for Teradata"  Hresult: 0x80040E0C  Description: "Command text was not set for the command object.".

    Thanks in advance,
    Nishant

    Tuesday, January 26, 2010 11:07 PM
  • Sounds like her solution was to either:
    a) Edit the expression to make it valid, or
    b) Refer to the correct variable in the OLE DB Source

    If those don't work for you, please post a new question to the forum with details about your situation, such as your source statement/expression, what variables are involved, and how you have the Source set up.
    Todd McDermid's Blog
    Tuesday, January 26, 2010 11:13 PM
    Moderator
  • Hi, I just had this problem and the workaround was that the variable was not initialized.

    When I set it up with a valid initial value; i was able to get it working without these errors.


    Regards, Sam Aaron
    Thursday, September 02, 2010 11:44 PM
  • You could also ensure the EvaluateAsExpression Property of the variable is set to True, as this will also cause the same error.

    Thanks,

    Don Sparks

    Tuesday, December 28, 2010 6:56 PM
  • This has been bugging me for the last day or two, because of the lack of a clear explanation by anyone about this.

    I've just got working an environment with effectively "self-defining" packages. The packages log their source statements to a table and then pull them back out again. This is to allow easy auditing of the builds in a datawarehouse.

    In order to load a SQL statement from a variable and use it in a data flow task, you need to create a dummy SQL statement in the variable that returns the same metadata as your source statement.

    For example here is a source table definition:

    CREATE TABLE mysource

    (

    id int

    , businesskey nvarchar(10)

    , businessdate datetime

    , businessvalue decimal(10,2)

    )

    In another table I have stored a SQL statement in an nvarchar(4000) column - this has an id attached to it so I know which one to use.

    I've created a string variable in my ssis package called Source and I know that the SQL for the OLEDB source component is "select BusinessKey, BusinessDate, BusinessValue from mysource" when it's loaded at runtime - this happens to map to the destination table over the same column.

    The problem is that even if you delay validation on the package, you still get column mapping errors because the package can't map the columns dynamically. (Thanks for that, Microsoft!!)

    To work around this, I set the value of the Source variable to be the following before runtime

    SELECT convert(nvarchar(10),'a') BusinessKey, getdate() BusinessDate, convert(decimal(10,2), 1) BusinessValue

    You can now set your OLEDB source component to use the source variable and it will present the same metadata as the statement loaded into your variable at runtime, thus allowing you to create the column mappings without any problems or any need for overcomplicated scripting in .net

    If anyone finds this thread and finds the above useful feel free to blog it wherever and give me a nod.

    D.

    Tuesday, July 10, 2012 2:39 PM
  • Hi MShetty,

    This is what I've done to avoid the same error:

    1. Set EvaluateAsExpression to False because you are directly assigning a value through your script

    2. Set into a Value for your @myVal variable "Select" statement including all columns (but without 'where clause') that you are planning to use into the destination:

    "Select col1, col2, col3... from db.Users " and set Data type as a string

    Hope it will help you.

    -Elena Z.

    Thursday, July 12, 2012 5:15 PM
  • I just ran into this as I am new to SSIS, and just figured out what my issue was (different flavor :)).  Here's what I have:

    In control flow I have a Data Flow Task which grabs a list of ID's and puts them into an object.  That points to a Foreach Loop Container and for each ID in the object, I want to run a stored procedure with the ID as the input parameter for the SP.  i.e. "EXEC someStoredProcedureName " + (DT_WSTR)@[User::nameOfIDvariable]

    I created an executeSQL variable, set EvaluateAsExpression = True, and put the above in for the Expression.  In the Expression Builder window, everything checked out just fine however when setting up the OLE DB Source Editor, setting the Data access  mode to "SQL command from variable" and picking my User::executeSQL variable, I got the somewhat famous error:

    "Error at Data Flow Task [OLE DB Source [1]]: 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.".

    The way I resolved it was changing the SCOPE of the executeSQL variable. Originally, the scope was set at the package level.  I created a new variable with the scope set to Data Flow Task.  In every other way, the variable was identical to the one before it at the package level only this time, it worked!

    Hopefully this helps somebody else!  Using SSIS in Visual Studio 2008, SSMS 2008 R2

    Wednesday, March 20, 2013 2:19 PM