Calling oracle stored procedure from SSIS

• a2zwd

  SSCrazy

  Points: 2353

  More actions

  October 18, 2013 at 7:46 am

  #281584

  Hi,

  I need to import data from Oracle 11g to Sql server.

  I have created the SSIS package. Datasource created. Test connection succeeded.

  Opened OLEDB Source and select DataAccess mode sql command.

  Typed the select statement Select * from tableName

  Preview -> Returning empty resultset but showing all columns.

  This is because we need to execute a procedure with access mode and regionid to get access the data from oracle.

  I tried the command in oledb source

  exec begin RGN.set_context_server(p_access_mode='S', p_region_id=121); end;

  select * from tablename

  I am getting error "command was not prepared"

  What is the issue.

  Thanks

• sestell1

  SSChampion

  Points: 10230

  More actions

  October 18, 2013 at 10:37 am

  #1659803

  You can call an Oracle stored procedure from an Execute SQL Task by using the following syntax:

  {CALL <schema>.<procedure>}

  In your case I believe it would be

  {CALL RGN.set_context_server(p_access_mode='S',p_region_id=121)}

  Although I've never tried this with parameters.