July 22, 2009 at 9:51 am
I created a SSIS package, the source data is from sql server, and destination is Oracle. I used Oracle provider for OLEDB. The loading process is really slow, it took more than a hour to load 220,000 records. Do you know if there is a way to make my load fast?
July 22, 2009 at 1:06 pm
Is it possible for you to check the results of sp_who2 for this particular process when you are transferring records from SQL to Oracle? It could be your slow network or blocking or something else but atleast u will get pointed in some direction by checking that.
July 23, 2009 at 4:17 pm
What is the Data Source query look like? Is it a simple or complex query with a lot of joins? Try running it in SQL Server Mgmt Studio to see what the performance is like. You could try to extract the data to a text file and then load this data from text file to Oracle. This is to test and may help you identify the bottleneck.
Is there an index on the Oracle table where the data is getting loaded to? If there is an index on the table then it would slow the load as it will require the indexes to be updated. It is a good practice to drop index first, load data and then recreate the indexes.
The slow performance could be due to many factors.
Hope that makes sense.
July 26, 2009 at 2:37 pm
This issue always being in place. I am using 3rd party driver by CozyRoc SSIS+. Worked great! 20 mln records in 30 min.
July 27, 2009 at 5:00 am
Can I get CozyRoc SSIS+ for free? I searched online for CozyRoc SSIS+ this morning, and I got page not found message for cozyroc.com.
July 29, 2009 at 5:30 am
You can use the Microsoft Connectors for Oracle by Attunity which is free and offers better performances than the standard SSIS component.
Yann Nguyen France
MCITP: Business Intelligence Developer 2008
July 29, 2009 at 7:28 am
yann_nguyen (7/29/2009)
You can use the Microsoft Connectors for Oracle by Attunity which is free and offers better performances than the standard SSIS component.
Article says
Microsoft Connector for Oracle by Attunity
The Microsoft Connector for Oracle is a set of managed components for transferring data from or to an Oracle database. The component is designed to be used with the Enterprise and Developer editions of SQL Server 2008 Integration Services.
thats it is designed for SQL 2008. Does it works with SQL 2005 too?
July 29, 2009 at 8:29 am
I replaced Microsoft Oracle Connector by Attunity for my Oracle destination, and the process is really fast!
July 29, 2009 at 8:33 am
Yes I tested it on sql server 2005 with sucess. Indeed as said above the best performance is often obtained by exporting to a text file from SQL SERVER and use the Oracle SQL loader.
Yann Nguyen France
MCITP: Business Intelligence Developer 2008
July 29, 2009 at 9:29 am
Thanks, that was a great information.
July 31, 2009 at 8:56 am
The bad news is MS Oracle Connector by Attunity is designed to be used with the Enterprise and Developer editions of SQL Server 2008 SSIS. I have Standard Edition SSIS installed. I can run the package within BIDS, I cannot run package using command line (batch file). Here is the error message: The component "DW" (716) cannot run on installed Standard Edition of Integration Services. It requires Enterprise Edition or higher. I have to find another way for the Oracle loading performance.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply