Making SSIS and Oracle play nice

SSISandOracleBFF.mkd

##Packaged stored procedures carp with the following error

failed with the following error: "ORA-01858: a non-numeric character was found where a numeric was expected

Add this to the end of your Oracle Connection Manager: UseSessionFormat=true

##Codepage warnings

Update this property in your Oracle ConnectionManager

```AlwaysUseDefaultCodePage=True``

##SQL Task with a simple out variable

Not to be confused with resultsets

###Tabs

####General

• CodePage: 1252
• Connection Type: OLE DB
• BypassPrepared: True

####Parameter

• Direction: Output
• Data Type: VARCHAR
• Parameter Name: 0
• Parameter Size: 1 (this should correlate to the max length of the returned data)

####Result Set and Expressions

remain as is

###Using Oracle Stored procs

let's assume you have the following table

``sql CREATE TABLE FOO( ID NUMBER(5) NOT NULL, MESSAGE VARCHAR2(50) );

then assum you have the following package (hurr)

```sql
CREATE OR REPLACE PACKAGE FOO_PCK
AS

  --types
  TYPE FOO_RefType IS RECORD (
    ID FOO.ID%TYPE,
    MESSAGE FOO.MESSAGE%TYPE
  );

  --cursors
  TYPE FOO_RefCur IS REF CURSOR RETURN FOO_RefType;

  -- stored pro headers
  PROCEDURE GET_CARTER(pCursor out FOO_RefCur);
END FOO_PCK

your package body looks like so

CREATE OR REPLACE PACKAGE FOO_PCK
AS  
  PROCEDURE GET_CARTER(pCursor out FOO_RefCur) IS
  BEGIN
    OPEN pCursor FOR
      SELECT ID, MESSAGE FROM FOO;
      
    EXCEPTION
    
    WHEN OTHERS THEN
      IF pCursor%ISOPEN THEN
        CLOSE pCursor;
      END IF;
      
    RAISE;
  END GET_CARTER;  
END FOO_PCK

You'll need to define a DFT (I've tried using SQL task to no avail, the Microsoft driver makes no effort to collect the results of a cursor into a rowset.

Add a Script Component Source to ADO.NET Oracle source. The type of connection is important.

Add your columns. Edit the script

Add a reference to System.Data.OracleClient

    public override void AcquireConnections(object Transaction)
    {        
        connMgr = this.Connections.AdoNetOracleConnection;
        oracleConn = (OracleConnection)connMgr.AcquireConnection(Transaction);        
    }
    
    public override void PreExecute()
    {
        string plsql = "FOO.GET_CARTER"; // <== this needs to match the sproc sig
        oracleCmd = new OracleCommand(plsql, oracleConn);
        oracleCmd.CommandType = CommandType.StoredProcedure;
        OracleParameter outRefPrm = oracleCmd.Parameters.Add("pCursor", OracleType.Cursor); // <== this needs to match the sproc sig, yes the parameter name needs to be identical
        outRefPrm.Direction = ParameterDirection.Output;

    }

    public override void PostExecute()
    {
        if (oracleCmd != null)
        {
            oracleCmd.Dispose();
        }
    }

    public override void ReleaseConnections()
    {
        connMgr.ReleaseConnection(oracleConn);
    }

    public override void CreateNewOutputRows()
    {
        try
        {
            
            OracleDataReader rdr = oracleCmd.ExecuteReader();

            while (rdr.Read())
            {
                Output0Buffer.AddRow();
                Output0Buffer.ID = Convert.ToSByte(rdr[0].ToString());
                Output0Buffer.MESSAGE = rdr[1].ToString();                
            }

            rdr.Close();

        }
        catch (Exception ex)
        {
            this.ComponentMetaData.FireError(-1, "InitExtract", ex.Message, String.Empty, 0, out fireAgain);
        }
    }