SSIS - Oracle Login Procedure

• ray-757028

  Old Hand

  Points: 357

  More actions

  May 23, 2008 at 5:17 am

  #186983

  I am writing a SSIS package and using the Oracle OLEdb connection rather then the MS one, I can query tables...

  I have been asked to run the following script on logging into Oracle (Oracle database is 9i but using the 10g client as they are planning on upgrading the database soon)

  ALTER SESSION SET NLS_LANGUAGE='AMERICAN';

  SET LINES 200;

  SET PAGES 12000;

  EXEC dbms_application_info.set_client_info('999');

  I can run the first line but after that I get errors

  So created a simple execute task (which does Parse)

  and get error( resultset is set to none...as it wont..)

  " failed with the following error: "ORA-00922: missing or invalid option". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  Task failed: Set Environment 1

  SSIS package "SSIS_AHG_Finance.dtsx" finished: Success.

  (I can login to SQL plus and run this)

  Regards

  Ray

• hmaharaj

  Right there with Babe

  Points: 781

  More actions

  June 25, 2008 at 12:57 pm

  #834223

  first choose ms ole provider for oracle, then for executing the procedure do this

  BEGIN

  EXEC proc;

  COMMIT;

  END;