locked
Need to lookup an Oracle Sequence value within a dataflow RRS feed

  • Question

  • I can successfully get an Oracle sequence value using an "Execute SQL Task" transform. I need to, however, be able to get the same type of value from within a dataflow. I've tried the "OLE DB Command" and the "Lookup" Transform without any luck.

     

    With the lookup transform I run into the following problem. First of all for those of you who know about the Oracle Sequence it just returns the next value from a sequence generator using "mysequence.NEXTVAL". The lookup transform won't just return a value without providing a link. So what I attempted to do was create a derived column with a value of "0" and use the following SQL command in my lookup ( SELECT mysequence.NEXTVAL, 0 AS DUMMY from DUAL). I then linked the derived column to the DUMMY column and it appeared to work. I was able to get the sequence. However, it appears to cache the value and so for each row the sequence is the same and doesn't increment.

     

    I then tried to unsuccessfully play around with nocache but had strange errors about "not a valid sql statement".

     

    I then also tried the OLE DB Command with a SQL statement "SELECT mysequence.NEXTVAL FROM DUAL" but couldn't get that to work.

     

    Does anyone have any ideas or recommendations. Please keep in mind that I HAVE to get the NEXTVAL from Oracle as this is a mandatory requirement since the source is Oracle Applications ERP and I can't make up my own sequences in SSIS or update that sequence after the fact since many other applications could be using that sequence besides me.

     

    thanks

    John   

    Wednesday, April 18, 2007 12:31 AM

Answers

  • I might have figured out a solution. I'll test it to verify.

    1. Create an OLE DB Command transform.

    2. Call a SQL Server stored procedure

    3. Reference the OPENQUERY command in the stored procedure

     

    I'm sure the performance won't be great but who cares ... 

    Friday, April 20, 2007 11:07 PM

All replies

  • I don't have an Oracle instance handy to test this, but here is a suggestion. Use a lookup with "SELECT mysequence.NEXTVAL, 0 AS DUMMY from DUAL" as the query. Go to the advanced tab and check the enable memory restriction box. You shouldn't have to change anything else on this tab.

    Theoretically, this should force the Lookup to execute the SQL statement for each row that passes through it.

     

    If this doesn't work, let me know. I can test it against Oracle tomorrow.

    Wednesday, April 18, 2007 1:22 AM
  • I'll try that. I don't remember if I already did as I tried a few different options using nocache and enabling memory restriction. I might have changed both at the same time. I'll try just the restrict memory option tomorrow.

    thanks for the suggestion

    Wednesday, April 18, 2007 3:18 AM
  • Enabling memory restriction alone doesn't appear to work. It looks like when I do that something else needs to be done ("Modify the SQL Statement" possibly). I'm just not sure what and how to modify that. Do you have any other ideas?

    thanks

    Wednesday, April 18, 2007 3:55 PM
  • What error message are you getting?
    Thursday, April 19, 2007 12:45 AM
  • When I go to advanced to enable memory restriction I have to do one of two things.

    1. Enable caching - which we don't want.

    2. Modify the SQL statement (not sure why and what needs to be changed).

     

    Once I pick option 2 I get the following error messages.

     

    Error 9 Validation error. Data Flow Task -  Update all column values: Lookup [13144]: An OLE DB error has occurred. Error code: 0x80040E14.  An OLE DB record is available.  Source: "Microsoft OLE DB Provider for Oracle"  Hresult: 0x80040E14  Description: "ORA-00933: SQL command not properly ended ". SPO_TO_ORACLE_PO.dtsx 0 0 
    Error 10 Validation error. Data Flow Task -  Update all column values: Lookup [13144]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties. SPO_TO_ORACLE_PO.dtsx 0 0 

    Thursday, April 19, 2007 1:23 AM
  • Why do you have change the SQL Statement?
    Thursday, April 19, 2007 1:48 AM
  • As soon as I enable memory restriction it errors out and displays those two errors.
    Thursday, April 19, 2007 1:57 PM
  • Sorry for the late response. I was trying to get my Oracle instance up, but the VM I use has some sort of issue. The error sounds like the SQL isn't being parsed properly. Did you fill in the parameters option on the advanced tab?
    Friday, April 20, 2007 3:15 PM
  • Given the command I'm using I can preview the next available sequence without any problem. However, as soon as I select memory restriction I have to use one of the two available options because of the errors I subsequently get.

     

    Error 11 Validation error. Data Flow Task -  Update all column values: Lookup [13438]: An OLE DB error has occurred. Error code: 0x80040E14.  An OLE DB record is available.  Source: "OraOLEDB"  Hresult: 0x80040E14  Description: "ORA-00933: SQL command not properly ended". SPO_TO_ORACLE_PO.dtsx 0 0 
    Error 12 Validation error. Data Flow Task -  Update all column values: Lookup [13438]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties. SPO_TO_ORACLE_PO.dtsx 0 0 

    After I select "modify the sql statement" the button becomes enabled for parameters. When I click on parameters I get another error message.

    ORA-00933 SQL command not properly ended.

     

    Looks like this is not achievable using the lookup transform. I even downloaded the Oracle driver without luck. Could this be achieved with an OLE DB command and/or stored procedure?

    thanks 

    Friday, April 20, 2007 3:43 PM
  •  J.A.J. wrote:

    Given the command I'm using I can preview the next available sequence without any problem. However, as soon as I select memory restriction I have to use one of the two available options because of the errors I subsequently get.

     

    Error 11 Validation error. Data Flow Task -  Update all column values: Lookup [13438]: An OLE DB error has occurred. Error code: 0x80040E14.  An OLE DB record is available.  Source: "OraOLEDB"  Hresult: 0x80040E14  Description: "ORA-00933: SQL command not properly ended". SPO_TO_ORACLE_PO.dtsx 0 0 
    Error 12 Validation error. Data Flow Task -  Update all column values: Lookup [13438]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties. SPO_TO_ORACLE_PO.dtsx 0 0 

    After I select "modify the sql statement" the button becomes enabled for parameters. When I click on parameters I get another error message.

    ORA-00933 SQL command not properly ended.

     

    Looks like this is not achievable using the lookup transform. I even downloaded the Oracle driver without luck. Could this be achieved with an OLE DB command and/or stored procedure?

    thanks 

     

    Maybe someone who has an Oracle instance handy could test this. The error indicates that the Lookup isn't parsing the SQL statement properly. That is likely a driver issue. Which driver are you using?

     

    Also, if you could post the SQL from the Modify SQL box, that might be helpful.

    Friday, April 20, 2007 3:57 PM
  • Here is an alternative approach:

     

    • Have a Execute sql task in control flow to retrieve CURRENT value of the sequence into a variable
    • In the dataflow have a script component to 'generate' and add the sequence column using the value of the previous step as a seed.
    • Back in control flow, use a second execute sql task to set the Oracle sequence to its new current value using the value in the variable.

     

    http://sqljunkies.com/WebLog/sqlbi/archive/2005/05/30/15684.aspx

     

    Notice that this can produce unexpected results if there is other processes using the same sequence while the package is running.

    Friday, April 20, 2007 4:31 PM
  • I've tried both an integer and varchar as the linking column. Here's the varchar one.

     

    select * from
     (SELECT po_headers_interface_s.NEXTVAL,'SPO' AS DUMMY
     FROM DUAL) as refTable
    where [refTable].[DUMMY] = ?

     

    I also tried an OLE DB Command hoping that I could call an Oracle procedure or function but here too I get some error messages. Looks like Oracle procedures are not called the same way as SQL Server ones (i.e. EXEC procedure ?, ? OUTPUT).

     

    I'm using the standard Microsoft OLE DB driver and I've downloaded the Oracle 9.0.2.x OLE DB driver.

    Friday, April 20, 2007 4:34 PM
  • I've thought of doing something like that. The one problem is that I'm not the only process in control of the sequence. This is a sequence which is available to any Oracle process calling it besides me. I can't assume that I will have the next available sequences in sequential order. I really need to be able to call the Oracle sequence in the data flow and receive the next available value whatever that might be because it could be out of order.

    John

    Friday, April 20, 2007 4:37 PM
  • I was hoping to also try the OLE DB command instead of the lookup but I can't get the Oracle wrapper/procedure to execute properly to return the sequence. I did find the following in some MS documentation. It appears that I'm trying to do something that's not even going to work. Has anyone heard anything about not being able to get result sets from an Oracle procedure?

     

    Although Microsoft supports MSDAORA, the Oracle provider for OLE DB (OraOLEDB.Oracle.1) is the one suggested for best results. This provider has been tested by the SSIS team, although it is not as well stress-tested as MSDAORA. OraOLEDB.Oracle.1 is available in 32-bit and 64-bit versions, however, and at this time is the only released 64-bit provider for Oracle. Although the OraOLEDB.Oracle.1 provider supports the new Oracle data types, neither this provider nor MSDAORA supports executing stored procedures with parameters that provide result sets. SSIS is unable to use the metadata APIs against the Oracle database.

    Friday, April 20, 2007 10:08 PM
  • I might have figured out a solution. I'll test it to verify.

    1. Create an OLE DB Command transform.

    2. Call a SQL Server stored procedure

    3. Reference the OPENQUERY command in the stored procedure

     

    I'm sure the performance won't be great but who cares ... 

    Friday, April 20, 2007 11:07 PM