locked
SQL query returns value but EXEC SQL Task fails to assign variable RRS feed

  • Question

  • I have an execute SQL task to get an identifier from an Oracle database and assign it to a variable (the variable is used in subsequent query's where clauses to transfer the data to a SQL Server database). If I copy the query from the SSIS package to the Oracle SQL Developer interface and run the query; I get the expected single identifier. If I run the execute SQL task I get the error "[Execute SQL Task] Error: An error occurred while assigning a value to variable "VariableName": "Single Row result set is specified, but no rows were returned."

    I can create phony/simpler versions of the query that will return the wrong identifier that will successfully assign the value to the variable, so the problem isn't with the variable itself or the way the result set is set up in the task (Result Set/Single Row in the General panel; Result Name 0/VariableName in the Result Set panel).

    How can two queries that return a single value produce different results with respect to the variable assignment process?

     

    Wednesday, January 22, 2014 6:32 PM

Answers

  • In trying to develop a work around I discovered the problem. While I checked and double checked that I was using the same Oracle server it was transparent to me that SSIS and SQL Developer were working on different transactions (working in SQL Server for so long tends to make one oblivious to this aspect of Oracle). Once the commit was done in SQL Developer, SSIS began returning the correct result.

    Thanks for taking the time to make the suggestions and I hope we all remember this one.

    • Proposed as answer by Mike Yin Friday, January 24, 2014 6:51 AM
    • Marked as answer by Mike Yin Sunday, February 2, 2014 2:12 PM
    Wednesday, January 22, 2014 9:27 PM

All replies

  • This is a SQL SELECT query, correct?  Not a stored procedure call.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, January 22, 2014 6:41 PM
  • I suspect the mapping is not done properly (VariableName).

    But without any technical insight it remains a guess.

    This thread may have an answer for you:

    http://social.technet.microsoft.com/Forums/sqlserver/en-US/af70f029-6ec9-4040-ad1e-f07d4f468b58/single-row-result-set-is-specified-but-no-rows-were-returned?forum=sqlintegrationservices which I deemed appropriate based on the error text you shared.


    Arthur My Blog

    Wednesday, January 22, 2014 6:45 PM
  • Would you mind posting the query atleast a stub? Ideally it should work fine based on your explanation as you say you have tried with similar query. Whats the datatype in Oracle for the field which is returned?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, January 22, 2014 6:45 PM
  • Yes it is a select.

    Wednesday, January 22, 2014 6:47 PM
  • Well that has to do with a similar problem but not the same, though I may need to cope with that problem as well later on. My query definitely returns a value and the problem in the thread linked is when it doesn't return a value.

    Thanks anyway.

    Wednesday, January 22, 2014 6:52 PM
  • I really can't post the query (proprietary stuff you know) but think of it this way:

    select id from table where ProcessedStatusColumn is null and DateColumn = (sub select to get most recent date in table where ProcessedStatusColumn is null)

    The id returned is a string (Oracle varchar2 type) into a string SSIS variable. Running the query in SQL Developer returns the correct id but SSIS fails with the same query.

    If I take out the part about the ProcessedStatusColumn, I'll get the wrong id but that one will be assigned to the variable without a problem.

    Wednesday, January 22, 2014 7:00 PM
  • Inspect the variable the part "no rows were returned" means there were none.

    So what do you get in one case vs the other?


    Arthur My Blog

    Wednesday, January 22, 2014 7:04 PM
  • The puzzle is how there can be "no rows returned" when running the exact same query (copy and paste) in SQL Developer does return rows. The implication is that SSIS sends a different query (that doesn't return rows) to Oracle than the one that is written in the task (which does return rows). I'd find it difficult to believe that SSIS could be that creative.

    In the case where it works, I get the wrong id in the variable; same column data, just the wrong one.

    Wednesday, January 22, 2014 7:12 PM
  • Perhaps you can trap (profile) the query in Oracle to see what it gets submitted,

    thing is this query

    select id from table where ProcessedStatusColumn is null and DateColumn = (sub select to get most recent date in table where ProcessedStatusColumn is null)

    is not guaranteed to return a value, you need a default returned in case the search failed e.g.

    select 
    NVL(id, 0)
    from table where ProcessedStatusColumn is null and DateColumn =
     (sub select to get most recent date in table where 
    ProcessedStatusColumn is null)


    Arthur My Blog

    Wednesday, January 22, 2014 7:24 PM
  • >The implication is that SSIS sends a different query

    No. SSIS will not change the query.

    Are you sure you are connecting to the same server with the same user in SqlDeveloper?

    SqlDeveloper uses JDBC.  What version of the Oracle client library and what driver are you using in SSIS?  It's possible that something like the regional settings on the two sessions are different.  Are you using any date or time literals in the query?

    As an alternative ou can return the value without using a resultset using PL/SQL.

    Set the SQLStatement to something like

    begin
      select cast('abc' as varchar2(20)) into :val from dual;
    end;

    Then in the Execute SQL Parameter Mapping, add one like:

    Variable Name = User::a

    Direction = Output

    Data Type = AnsiString

    Parameter Name = val

    Parameter Size = 100

    You can run this PL/SQL block both in SQLDeveloper and SSIS, and bypass the resultset processing.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Wednesday, January 22, 2014 7:26 PM
  • I wish I knew how to trap the query in Oracle especially since I have no DBA access there as it is on the client side.

    There's a difference between a null value being returned and no row being returned. In the case of no row being returned; NVL won't be able to supply an alternate value and therefor is no help in this case. This is a case where no row is returned in SSIS even though a row is returned with the exact same query in Oracle.

    Wednesday, January 22, 2014 7:32 PM
  • OK, then use

     select case when NOT EXISTS(SELECT id ... the rest


    Arthur My Blog

    Wednesday, January 22, 2014 7:42 PM
  • The context of my proposition that SSIS altered the query was intended to show that I didn't believe the proposition.

    Connecting to the same server was checked and double checked.

    I'm not sure how to check the client library, I'm using an OLEDB connection in SSIS.

    I'm not using any literals in the query.

    Your PL/SQL solution was the sort of thing I was looking for. I tried it but got a similar error about not getting any results, yet the query at the core still returns the correct, expected value in SQL Developer.

    Wednesday, January 22, 2014 7:55 PM
  • Which OleDB provider? 

    And if you're using OleDB then you have a copy of the Oracle client installed.  Somewhere in your path there's a copy of oci.dll.  That's the library that implements the Oracle Call Interface (OCI).  It's loaded as a Win32 DLL by dtexec and by Visual Studio, and has a version resource that indicates the client version level.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, January 22, 2014 8:00 PM
  • In trying to develop a work around I discovered the problem. While I checked and double checked that I was using the same Oracle server it was transparent to me that SSIS and SQL Developer were working on different transactions (working in SQL Server for so long tends to make one oblivious to this aspect of Oracle). Once the commit was done in SQL Developer, SSIS began returning the correct result.

    Thanks for taking the time to make the suggestions and I hope we all remember this one.

    • Proposed as answer by Mike Yin Friday, January 24, 2014 6:51 AM
    • Marked as answer by Mike Yin Sunday, February 2, 2014 2:12 PM
    Wednesday, January 22, 2014 9:27 PM
  • I have done that more times than I care to admit, and I didn't remember to suggest it.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, January 22, 2014 10:27 PM