Post reply

Using SQL command from variable mode in OLE DB Source - error

  • November 11, 2009 at 3:08 pm

    #140026

    Hello,

    I have a SSIS package where I am trying to do the following:

    1 - Query a table in my database and output the two results into two variables that are scoped for the entire package:

    SELECT LEFT(serial_number, 1) AS first_char,

    '"select serial_number, item_number, location, description, cost from tblSErialNumberInfo where location <> 401 and location <> 451 and location <> 501 and status = ''A'' and left(serial_number,1) = ''' + LEFT(serial_number,1) + ''' "' AS qry

    FROM tblSerialNumberInfo

    WHERE (location <> 401) AND (status = 'A') AND (LEN(serial_number) > 0) AND (location <> 451) AND (location <> 501)

    GROUP BY LEFTserial_number, 1)

    ORDER BY first_char

    2 - Loop through the results

    3 - inside the loop, create a data flow task that uses the second variable above (User::sql_qry) as it's source (OLE DB Source - "SQL command from variable") and then dump the data from the query stored in the variable into an excel spreadsheet.

    1 and 2 work. When I get to the third task and attempt to set the variable name for the source, I get the following error: Error at Data Flow Task: 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."

    I've tried messing around with breakpoints and right before the source executes, the variable appears to have the correct value in it. The EvaluateAsExpression attribute is set to true for the variable.

    Any help would be greatly appreciated.

    Thank you!

  • November 20, 2009 at 7:22 am

    #1082210

    I have the same problem. Did you find a solution?

    Thanks,

    B

  • November 20, 2009 at 7:51 am

    #1082242

    When you check the variable value before execute does this contain valid SQL? and does this SQL run correctly when you run it in SSMS?

    Is the variable in the correct scope for the data flow task to use?

    Also you can try running a trace against your database to see what is being executed..

  • November 20, 2009 at 9:25 am

    #1082312

    Try using "sql command" directly and not from within a variable (SQL command from variable). It is a syntax error for sure and you can better find the correct form.

    Also you can try to set some initial values to variable.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply