Hi, Can anyone tell me if it is possible to call Oracle SP in SSIS. If yes how do we do that?

Yes it is possible Use Execute SQL Task and set connection to your oracle DB. Read about EXECUTE SQL TASK. Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/

as Rahul said, you can use Execute SQL task for it. you should create a connection to oracle DB and then write your sql statement in SQLStatement property, you can pass input parameters to this statement, or save result of this statement to a variables. Now if you have problem on connection from SSIS to oracle, use ODP.NET: http://www.oracle.com/technetwork/topics/dotnet/index-085163.htmlhttp://www.rad.pasfu.com

In my case im loading data from oracle source to SQL destination is ssis. The oracle queries are all written in SQl SP's We will have to create SP in oracle and use it as source. do you have any idea on this. it would be helpful

How do i declare the variable in Execute SQL task, using Oracle as the connection manager? for eg: this is code which i have written in Execute SQL task Declare @date datetime Select @targetdate = convert(varchar(20), isnull(max(time),'01/01/1999'), 20) as date from OracleDB.OracleTable I have set the variable name and resultset name in the result Set property of the Execute SQL task. Can you help out to solve this.

if you want to use result of stored procedure into a destination don't use execute sql task use OLEDB Source instead, and set data access mode as sql command, and write your sql statement in sql command, then put a destination to sql server, and map columns.http://www.rad.pasfu.com

I have done the same as using the oledb source, any used sql command. But here i have to pass a vaiable in the sql command, when i click on parameters tab in oledb source it gives an error .

Is it possible to call oracle SP in Oledb Source and pass parameters? like exec <SP name> @param = ? This will work if im calling an SP from SQL, but will i be able to do this if im calling an SP from Oracle. Any suggestion would be of great help

OK, when you want to use input parameters in your sql statement, use Parameter Marker, in the OLEDB Connection, parameter marker is : ? for example your sql command should be like this: select * from mytable where field1=? and field2=? this means that you have two parameters in your sql command now click on parameter button,and map these parameters with appropriate package variableshttp://www.rad.pasfu.com

This is working if im commected to SQL connection manager. But im connected to Oracle Provider for OLEDB source. It gives me a error "Parameter cannot be extracted from the SQL command. The provider might not help to parse the parameter information from the command Provider cannot derive parameter information and Setparameter infor has not been called(oraOLEDB)"

let me know what is your sql command exactly?http://www.rad.pasfu.com

I am just trying to call an SP from oracle with parameters in SSIS using Oracle Provider as OLEDB source

Any answer..?

1. Create a user variable for required parameters to pass in sp in execute sql task and assign it to the result set 2. Create OLEDB source and select sql command from variable and write the expression by passing your variables as a parameter EX: "SELECT * FROM Table >= TO_DATE('"+ @[User::V_EXTRACT_FROM_DT] +"', 'DD-MM-YYYY') AND SETT_DATE < TO_DATE('"+ @[User::V_EXTRACT_TO_DT] +"', 'DD-MM-YYYY') +1 " Here in the above example passing a where condition to a table, same way proceed with your SP

I am just trying to call an SP from oracle with parameters in SSIS using Oracle Provider as OLEDB source I need to know if you provide correct syntax with parameter markers in SQLStatement or not if you need more detailed help, you need to provide more detailed information from your package.http://www.rad.pasfu.com