How can I increase the maximum number of parallel connections when exporting to Oracle via

Hi to everybody,
When exporting to Oracle from SQL Server using Import/Export Wizard,
I've discovered that SQL Server only creates 4 simultaneous
connections to Oracle. Obviously this fact prevents SQL Server from
exporting the tables in a parallel way. Instead, the tables are forced
to wait until one of the 4 connections gets free to export and due to
this fact the performance of the export operation suffers from an
enormous slow down.
Even though I create new connections in the DTS Designer (up to 20),
SQL Server 2K continues only using the same 4 ones which makes me
suspect that there must be a hidden option somewhere to change this.
Is there any way in SQL Server 2K to increase the number of maximum
simultaneous connections to Oracle?
Thank your from beforehand for reading my post.
Greetings,
David Grant
By default, DTS executes only 4 tasks concurrently. Check how to change this
at
http://msdn.microsoft.com/library/de...sgnr2_60mp.asp
(the last option on the General tab of package properties).
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0504181032.2b96883c@.posting.google.c om...
> Hi to everybody,
> When exporting to Oracle from SQL Server using Import/Export Wizard,
> I've discovered that SQL Server only creates 4 simultaneous
> connections to Oracle. Obviously this fact prevents SQL Server from
> exporting the tables in a parallel way. Instead, the tables are forced
> to wait until one of the 4 connections gets free to export and due to
> this fact the performance of the export operation suffers from an
> enormous slow down.
>
> Even though I create new connections in the DTS Designer (up to 20),
> SQL Server 2K continues only using the same 4 ones which makes me
> suspect that there must be a hidden option somewhere to change this.
> Is there any way in SQL Server 2K to increase the number of maximum
> simultaneous connections to Oracle?
>
> Thank your from beforehand for reading my post.
> Greetings,
> David Grant
|||In addition to Dejan's excellent advice, note that you will need to look at how optimal hiking the tasks in parallel actually is.
If you are running on a 4 way then hiking this value to 20 would not be a good thing and you would see a lot of context switches as
things come in and off the processors. I would start with CPU Count -1

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in message news:%23RTgwCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> By default, DTS executes only 4 tasks concurrently. Check how to change this
> at
> http://msdn.microsoft.com/library/de...sgnr2_60mp.asp
> (the last option on the General tab of package properties).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "David Grant" <icebold54@.hotmail.com> wrote in message
> news:18503386.0504181032.2b96883c@.posting.google.c om...
>