.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Data movement between Oracle to SQL

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :Sql Server

I have trying to setup SSIS package, Here is my requirement,

I need to move data from a Oracle View to a oracle table and move data oracle table to SQL Table for further processing.

If I pull data from Oracle view (joining around 18 tables) takes more time to read from Oracle to insert into SQL Server. To avoid timing's , I am thinking to insert data from Oracle view into Oralce table and then table to SQL Server.

If I move data from Oracle view to Oracle table and this task is called from SSIS, Is this data movement is local to oracle or move across network.



View Complete Post

More Related Resource Links

Need Oracle Data Provider .CS File for Oracle 10g Database connection !



I need a 'Wrapper.cs' file which takes care of the Database connection ( Oracle 10g) where

i can just call the method with my SQL Query


Gridview1.DataSource = SampleWrapper.ExecuteDatatable("THE SQL QUERY");


Plz Post the link if there is any open source !    

Trying to run a stored procedure from vb code with oracle data provider.



Here is my SP:

create or replace
 Open p_getuserssignon_recordset1 for
 SELECT Distinct(Userid), UserPassword, SecurityLevel, ActiveStatus
WHERE substr(UserId,1,2) <> vUid
Order By UserId;

I would like to run this SP from code and fill a gridview with the result. 

I am not sure how to go about this, as I have found several different examples, other than the one I  think I need.

I am using the oracle data provider and I have an input parameter (vUid, which will equal "zz").

First question. When filling a gridview with a result set from a stored procedure should the recordset OUT be defined as a REFCURSOR (like i did above)? 


Do you have example code as to how to execute the SP and fill a gridview?  I keep trying different variations of code i've found on the internet without any success other than getting more confused.

(I am using VS 2005, VB).

Thank you.



"An error occurred while retrieving data from Oracle Instance..."


I have succeffuly Imported the ADF and have also successfully created a Business Data Column. But when I try to query data , I get the following error

An error occurred while retrieving data from Oracle Instance. Administrator, see the server log for more information

And this is what I found in the Application Logs
A Metadata Exception was constructed in App Domain '/LM/W3SVC/81256521/ROOT-1-129217212004078822'. The full exception text is: LobSystem could not be found using criteria 'id=476'.

No Way to retrieve data from oracle ref:_cursor over T-SQL and linked Server?

Hello, for an migration projekt we want to compare results from sp's from oracle and sqlsserver. Same calls should retrieve same results. Also we want build an automatic test for this. But is there now way to retrieve results from oracle sp's whit rev_cursor over linked Server? No one answer to this: http://social.msdn.microsoft.com/forums/en-us/sqldataaccess/thread/2BAC6743-8701-4476-8F36-0377A5761525   greetings Michael

Need Information on pulling data from Oracle Source to SQL Server

Hi,   I was told that we will be extracting data from Oracle database and loading into SQL Server Database. I will be developing packages on the Remote Server where SQL Server is installed. I mean I wont be developing anything on my local machine. This is the first time I will be writing a package to extract data from Oracle database and my knowledge on Oracle is zero.   Could someone list out  what all I need to do to extract data from Oracle database ?  Server that I will be working is new and nothing is installed on that except the SQL Server 2008.      

Analysis Services Using Oracle as a Data Source Data Types mismatch System.Int32 and System.Int64

Hi everyone, I searched for this issue but there is only 4 questions about it in this forum from 2007 I hope that someone can help me. Im usign SASS 2008 using a Oracle as a datasource.  I already have my fact tables linked with dimensions, lets say, FACTID System.Int64 with DIMENSIONID System.Int64 (My dimensions are replaced by named querys and work perfectly) I add a new table to my DSV and the NEWDIMENSIONID looks like System.Int64, but at the moment I replace the table with a named query (I have to use Oracle quering) the NEWDIMENSIONID changes to System.Int32 .  After that I cant relate the fact table with the dimension table. Actions: Looked for the columns that relate from Oracle... both are NUMBER(5). Looked for the differences between my other tables that are already related as System.Int64 and there is not a single difference between the ones that works and the one who doesnt. Solutions Ive tried and didnt work: 1. Cast the column as SQL query didnt work it send an error.  Looks kind of obvious because we are quering Oracle... but I had to try.  CAST( X AS BIGINT) 2. Cast the column as Oracle query . TO_NUMBER 3. Do it inside the server using the Analysis Services Enterprise insted of my Analysis Services Developer. Same issue.   Any ideas? suggestions?   Please? Thank you very much ;) Sincerly Adriana L. PS. At the begging of

Analysis Service Oracle Number inconsistent Data Type for TABLE or Named Query

Dear Gurus, I'd VERY OLD PROBLEM. And I believe it addressed since 2006. When I design DataSource Views from Oracle Data Source. I found it return different oracle number data type for TABLE or NAMED QUERY   Provider Data Type Column Data Type Data Source View Data Type Oracle OLE DB Provider (OraOLEDB.Oracle.1) Table Number System.Int64   View Number System.Decimal   Named Querey Number System.Decimal Microsoft OLE DB Provider for Oracle (MSDAORA.1) Table Number System.Double   View Number System.Double   Named Query   1 System.Int64   Named Query   1.1234 System.Int64 Althought I know I can fix IT via MANUALLY EDIT DATASOURCE VIEW XML SOURCE. But I don't think this is a better solution. Is anybody have ideas ?  Wilson

Transforing the Data from Oracle to SQL Server 2008

Hi all I have to create a SSIS package which can Transfer the Data from Oracle to SQL Server 2008. Every time when packages run it will suppose to create tables in SQL Server Database. I guess it is possible with export and Import, but the privies tables suppose be deleting before or after dumping.   Suggest me best method to do this.     Thanks in advance     SNIVAS

Importing Data from Oracle 10g to Sql Server 2005 using Linked Server

Hi,   I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :   OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error". OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.   Please let me know.   Thanks

SSIS Package for Delta Data between Oracle and SQL Server


Hi all

I have BI Server and Tables and Data Populated using SSIS package.Now Data is ready in BI server.Next week Ihave to same excersize to filll the Data(DROP tables,CREATE table and Pump the Data)

Can any body give an idea how to create SSIS package which can pupm the Delta Data Oracle and SQL Server.


Thanks in advance




Problem using SSIS to move DB2 data to Oracle



I will need your advice on a SSIS package which moves IBM DB2 (for z/OS V7) data to Oracle (AIX 10g). Thank you in advance.

Basically, this is a very simple SSIS package (created by SSIS import / export wizard) and just maps column by column. I used IBM OLEDB provider for DB2 to connect the source and Oracle Provider for OLEDB to connect to the destination.

The problem I ran into was that if the DB2 source value contains special (or invisible) characters then this value will be converted to a SPACE (' ') value on Oracle. To be more detailed, take the following example:

On DB2, value  000002D0000002D7 (EBCDIC HEX codes). This is actually a value of a column defined on DB2 as CHAR(8). Please note HEX '00' in EBCDIC stands for NULL and HEX 'D0' stands for character 'K';

On Oracle, this value will be stored as a SPACE (or a string of SPACEs) after data move.

On both DB2 and Oracle this column was defined as CHAR(8).

I guessed this was related to a code page issue, so I tried a few different code page to set for the source property (by setting the DefaultCodePage of the DataFlow source property), I used code page 37, 500, etc... All resulted in the same Oracle value.

The character set on DB2 is EBCDIC and the one on Oracle is ALT32UTF8. I guess it was the source provid

load data to an Oracle destination


hi all,

1.how to fastload data to an Oracle destination.

2.what if i need to add an identity column to the destination table.

  Is a sequence needed? how can ssis2008 handle that?


thanks in advance.

ORA-06550, PLS-00306; Error inserting data to Oracle procedure.


I have tried to troubleshoot this problem but to no avail. I get error while inserting a big chuck of data to Oracle package containing procedure 'INSCRAPP'. The error message is,

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'INSCRAPP' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

The C# code is big but I am providing the parameters I am passing,


                OracleParameter param_fName_in = new OracleParameter("fName_in", OracleType.VarChar);
                param_fName_in.Value = FirstName;
                param_fName_in.Direction = ParameterDirection.Input;
                OracleParameter param_mi_in = new OracleParameter("mi_in", OracleType.VarChar);
                param_mi_in.Value = MiddleInitial;
                param_mi_in.Direction = ParameterDirection.Input;

                OracleParameter param_lName_in = new OracleParameter("lName_in", OracleType.VarChar);
                param_lName_in.Value = LastName;
                param_lName_in.Direction = ParameterDirection.Input;

                OracleParameter para

SSAS 2008R2 - Enable to process a database using Oracle OraOLEDB data provider



I've created a data source in BIDS using provider OraOLEDB.Oracle.1. The Oracle database is 10gR2. The client installed on my computer is 10gR2. The connexion is OK when I use Test Connexion in BIDS.

In the data source view I created a very simple query: select * from user_objects. All is fine, the fields are correctly defined.

I've created a dimension based on this query with object_id as the key.

I've deployed the solution to the server. All is working fine for the moment.

But when I process the dimension I get a dozen of error messages like (sorry error messages are in french) :

"Erreur OLE DB : Erreur OLE DB ou ODBC : Syntax error near 'OraOLEDB.Oracle.1' on line 8; 42000."

Erreurs dans le moteur de stockage OLAP : Une erreur s'est produite lors du traitement de dimension portant l'ID 'Test' et le nom 'Test'.

Erreurs dans le moteur de stockage OLAP : Une erreur s'est produite lors du traitement de l'attribut 'SUBOBJECT NAME' de la dimension 'Test' de la base de données 'CTRL_DOMAINES'.

Serveur : L'opération a été annulée.

Erreur OLE DB : Erreur OLE DB ou ODBC : Syntax error near 'OraOLEDB.O

Openquery - truncation problem (converting data from Oracle 10g to SQL 2005)


I am having problems with data truncating when inserting into a SQL 2005 db from Oracle 10g- using openquery and a linked server.   For instance, if I run something like:

insert dbo.CWDocumentStaging
select * from openquery(LK_Snomass,'select RSFORM.REPRESENTATION as REPRESENTATION

The results of the blob data are only 202 hexademical characters in length.  And then when I convert it to varchar, the results are cut-off.  For instance, on a particular test record that I converted, here is what I have in the SQL database (using):

select convert(varchar(max), DOCUMENT_TEXT), DOCUMENT_TEXT from dbo.CWDocumentStaging

I get the following:

for Document_Text, I get the following:


then for convert(varchar(max), DOCUMENT_TEXT):

this old man, he played one, he played knick-knack on my thumb with a knick-knack paddy whack give a

If I insert the data running the query against the linked server directly, the data does not truncate

Replicate data from Oracle 10g to SQL 2008?



I need to replicate about 20 tables from Oracle 10g to SQL 2008 nightly.  I wonder if anyone knows or have done
this kind of task before or know what web site showing steps by steps setting it up.  I hear that you can use SSIS
to setup the task but never try it before.  I try to google it but nothing really showing how to set them up.
Any helps would greatly appreciate.



missing oracle provider for ole db in data link properties of adodc1 in my vb with already installed


missing oracle provider for ole db in data link properties of adodc1 in my vb with already installed oracle client...

please help how to have this "oracle provider for ole db"

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend