Load data using Oracle SP in SSIS

Question

Load data using Oracle SP in SSIS

josefrancis

SSC Enthusiast

Points: 150
More actions
October 11, 2008 at 11:41 pm

#126965

Hi,
I am very new to SSIS. I want to load data using an oracle stored procedure to a SQL server database using SSIS. How can we proceed. I tried different combination. But nothing worked out for me. Any help will be greatly appreciated.
Thanks
Jose

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Prachi-885067 SSC Veteran Points: 208 More actions · Answer 1

Did you try accessing Oracle SP by creating a system DSN?

Create a ODBC data source for Oracle(Control Panel -> Administrative Tools -> Data Sources(ODBC) -> System DSN -> Add ->select Microsoft ODBC for Oracle).

In SSIS connection manager tab, add new ADO.NET connection manager, go to Provider drop down, select ODBC Data Provider (created for Oracle) from drop down list.

This ODBC connection can be accessed using DataReader Source in Data Flow Task. You can use stored procedure in DataReader Source.

Note that ADO.NET connection manager does not support varchar data type. So automatically columns with varchar data type will be converted to nvarchar data type. In data flow task nvarchar data type can be converted back to varchar.

Try it and let me know.

Chris Campbell-415954 SSCrazy Points: 2248 More actions · Answer 2

You can not pass parameters directly to an Oracle OLE DB Source in SSIS, you must build the SQL dynamically in a SSIS variable and use the SQL Command from Variable option in your SSIS OLE DB Source. I also belive you must use slightly different syntax to call the Oracle procedure.

Here's a thread that discusses your issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1518879&SiteID=1

Hrvoje Smolic Grasshopper Points: 23 More actions · Answer 3

HI!

I have exactly the opposite requirement.

I need to move data between tables in SQL2005 to Oracle.

I used to do things the other way around with no problems.

But now, i have two connections, an OLE DB for SQL2005 and ADO NET for Oracle.

For my source I use OLE DB Source component in SSIS task, but what about destination?

I tried DataReader Destination (as I used DataReader source for READING from Oracle), but it just doesn't seem to work.

So, how to load data TO Oracle FROM SQL2005?

thanks...

nishav2 SSCrazy Points: 2194 More actions · Answer 4

Hi,

I need to load data from SQL table into Oracle Table.In that one of the column should be encrypted and stored in oracle table.How to achieve this in SSIS.I have successfully transferred the data from SQL to Oracle table.But now the requirement is i have to encrypt at the oracle side.

Can someone help me with this ?

Any help appreciated.

Thanks

Nisha.V