Importing from Oracle 10g

I am trying to import a variety of data from an Oracle 10g database. When I import the data using the OLE DB it takes a long time (100K records an hour).
How can I improve the throughput of my import?
Is there a better driver I could be using?
Any input will be appreciated.
db55

Some interesting reading for you:

http://www.sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx

http://microsoftdw.blogspot.com/2005/11/how-to-load-oracle-with-ssisfast.html

-Jamie

|||The second link gives another link to a 3rd party solution that could work. Thank you for your response.|||

Please create a ADO.NET Connection manager and use Data Reader Source..there will be a sure Improvement, over oledb.

Is the Data transferred over the Congested network. This might also reduce the performance.

Please let me know the increase of the performnce, once you use Ado.net Connection manager.

Importing from Oracle 10g

I am trying to import 6M records in a table from an Oracle 10g database
into a SQL Server 2000 database.

After importing 1.5M+ records I get the following error:

Ora-01555: snapshot too old: rollback segment number 129 with name
"_SYSSMU129$" too small

How can I speed up the import process between Oracle and SQL Server?
The database box I'm using has 12GB of memory and 1.5 TB of space. It
shouldn't be a hardward problem. :-)

Thanks in advance.One possible cause is that your Oracle UNDO tablespace isn't sized large
enough to accommodate data modifications made during the import. In that
case, you might try running the process during a time of less update
activity or increase the UNDO tablespace size.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"db55" <chfran@.gmail.com> wrote in message
news:1138139643.416635.125020@.g14g2000cwa.googlegr oups.com...
>I am trying to import 6M records in a table from an Oracle 10g database
> into a SQL Server 2000 database.
> After importing 1.5M+ records I get the following error:
> Ora-01555: snapshot too old: rollback segment number 129 with name
> "_SYSSMU129$" too small
> How can I speed up the import process between Oracle and SQL Server?
> The database box I'm using has 12GB of memory and 1.5 TB of space. It
> shouldn't be a hardward problem. :-)
> Thanks in advance.|||Thank you. I will contact the Oracle DBA.

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

MSDAORA does not support 10g, have a look at INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider
Try using OraOLEDB.Oracle instead.|||

Hi Anton,

Thanks for speedy reply.

Anton in my procedure if I remove transaction(Begin Tran, Commit, Rollback) then this procedure is working fine, but removal of it not possible, so it is not related with provider, I hope.

If I put tranaction it shows above maintain error.

Thanks

|||Is there any reason you cannot try Oracle's provider?|||

Hi Anton,

I am using OLEDB.ORACLE but it is not allowing me to make new Linked server.

It shows me this error:

TITLE: Microsoft SQL Server Management Studio

"The linked server has been created but failed a connection test. Do you want to keep the linked server?"

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB provider "OraOLEDB.Oracle" for linked server "AA" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "AA". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

BUTTONS:

&Yes
&No

|||Have you enabled AllowInProcess option in the provider options?|||

Hi Anton,

Yes, I did but it returns another error:

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Procedure PROC_VLD_BI_TRANSFER, Line 36
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS".

|||But MSDAORA is not an Oracle's provider. Did you try it for the OraOLEDB?|||

Hi Anton,

Yes, I did

But it is not allowing me to make a new linked Server.

Presently I am facing this problem:

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Procedure PROC_VLD_BI_TRANSFER, Line 36
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS".

|||

Hi Anton,

Now I solved ORA-12154 Error but when I enabled "Allow InProcess" this gives ORA-12154 Error again.

After disable this option, I can connect Oracle server successfully but when I used to fire my procedure that is used to import records from oracle 10g to sqlserver 2005 using linked server.

It Shows me this error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" reported an error. The provider did not give any information about the error.
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" was unable to begin a distributed transaction.

Thanks for your nice support.

|||http://support.microsoft.com/kb/306212
http://support.microsoft.com/kb/816701|||

Hi Anton,

I got 2 kind of error:

checked "Allow inprocess" in the provider options, then it Error:

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS".

If NOT CHECKED then 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.

I used later one because by this i can access records from oracle but when i used this linked server in my Procedure using transaction then this error is reflected.

I find out a link of support.microsoft.com/kb/906954 but it can not make me out from this trouble.

Please advise me the solution.

Thanks for your support.

Importing data from oracle 10g to SQL Server

Hi,

I created SQL Server package and schedule the job. SQL Server allows us to connected different database for e.g. for oracle using "Oracle Provide for OLD DB" to retrieve our database. A link is create between this database which move the data to SQL Server. I'm sure there is no issues in the link, as I'm using it to retrieve several oracle database which contains both Arabic and English data.

But after Importation it is showing Junk values. Please advise me what step I should take next.

Regards

Azeem

Did you already pump the data in a table ? Did you make sure the data table can hold unicode data (data types have to be of type NVARCHAR / NCHAR or a similiar data type supporting Unicode, look in the BOL concnering types which support storing unicode with the N at the beginning)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

You may need to alter your collations on the SQL Server side for unicode fields to ensure that they are appropriate for the text you are storing. Books online has more information about collations and unicode data.

|||

Hi Azeem,

Would you be able to provide more information or a sample of what you mean under "junk values"? Also - the collation and the nchar/nvarchar ideas mentioned above are definitely required prerequisites in this case.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Azeem Anzari wrote:

Hi,

I created SQL Server package and schedule the job. SQL Server allows us to connected different database for e.g. for oracle using "Oracle Provide for OLD DB" to retrieve our database. A link is create between this database which move the data to SQL Server. I'm sure there is no issues in the link, as I'm using it to retrieve several oracle database which contains both Arabic and English data.

But after Importation it is showing Junk values. Please advise me what step I should take next.

Regards

Azeem

You are getting character conversion you need to use Nvarchar for the Arabic column and use Arabic collation, you should also know Lam-Alef glyphs Arabic code pages does not exist in SQL Server you may need to use VS2005 to use Advanced save as option. Post again if you still need help.

http://msdn2.microsoft.com/en-us/library/ms144250.aspx

http://msdn2.microsoft.com/en-us/library/ms180175.aspx

Importing data from oracle 10g to SQL Server

Hi,

I created SQL Server package and schedule the job. SQL Server allows us to connected different database for e.g. for oracle using "Oracle Provide for OLD DB" to retrieve our database. A link is create between this database which move the data to SQL Server. I'm sure there is no issues in the link, as I'm using it to retrieve several oracle database which contains both Arabic and English data.

But after Importation it is showing Junk values. Please advise me what step I should take next.

Regards

Azeem

Did you already pump the data in a table ? Did you make sure the data table can hold unicode data (data types have to be of type NVARCHAR / NCHAR or a similiar data type supporting Unicode, look in the BOL concnering types which support storing unicode with the N at the beginning)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

You may need to alter your collations on the SQL Server side for unicode fields to ensure that they are appropriate for the text you are storing. Books online has more information about collations and unicode data.

|||

Hi Azeem,

Would you be able to provide more information or a sample of what you mean under "junk values"? Also - the collation and the nchar/nvarchar ideas mentioned above are definitely required prerequisites in this case.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Azeem Anzari wrote:

Hi,

I created SQL Server package and schedule the job. SQL Server allows us to connected different database for e.g. for oracle using "Oracle Provide for OLD DB" to retrieve our database. A link is create between this database which move the data to SQL Server. I'm sure there is no issues in the link, as I'm using it to retrieve several oracle database which contains both Arabic and English data.

But after Importation it is showing Junk values. Please advise me what step I should take next.

Regards

Azeem

You are getting character conversion you need to use Nvarchar for the Arabic column and use Arabic collation, you should also know Lam-Alef glyphs Arabic code pages does not exist in SQL Server you may need to use VS2005 to use Advanced save as option. Post again if you still need help.

http://msdn2.microsoft.com/en-us/library/ms144250.aspx

http://msdn2.microsoft.com/en-us/library/ms180175.aspx

Importing data from oracle 10g to SQL Server

Hi,

I created SQL Server package and schedule the job. SQL Server allows us to connected different database for e.g. for oracle using "Oracle Provide for OLD DB" to retrieve our database. A link is create between this database which move the data to SQL Server. I'm sure there is no issues in the link, as I'm using it to retrieve several oracle database which contains both Arabic and English data.

But after Importation it is showing Junk values. Please advise me what step I should take next.

Regards

Azeem

Did you already pump the data in a table ? Did you make sure the data table can hold unicode data (data types have to be of type NVARCHAR / NCHAR or a similiar data type supporting Unicode, look in the BOL concnering types which support storing unicode with the N at the beginning)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

You may need to alter your collations on the SQL Server side for unicode fields to ensure that they are appropriate for the text you are storing. Books online has more information about collations and unicode data.

|||

Hi Azeem,

Would you be able to provide more information or a sample of what you mean under "junk values"? Also - the collation and the nchar/nvarchar ideas mentioned above are definitely required prerequisites in this case.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Azeem Anzari wrote:

Hi,

I created SQL Server package and schedule the job. SQL Server allows us to connected different database for e.g. for oracle using "Oracle Provide for OLD DB" to retrieve our database. A link is create between this database which move the data to SQL Server. I'm sure there is no issues in the link, as I'm using it to retrieve several oracle database which contains both Arabic and English data.

But after Importation it is showing Junk values. Please advise me what step I should take next.

Regards

Azeem

You are getting character conversion you need to use Nvarchar for the Arabic column and use Arabic collation, you should also know Lam-Alef glyphs Arabic code pages does not exist in SQL Server you may need to use VS2005 to use Advanced save as option. Post again if you still need help.

http://msdn2.microsoft.com/en-us/library/ms144250.aspx

http://msdn2.microsoft.com/en-us/library/ms180175.aspx