Hello All,

We developed a DTS package that would pull records from 12 different source
tables and inserts into 12 different target tables.
For this, we created 12 different "Transform Data Tasks" that run in
Parellel.

However while testing we found that it Transfers only 10 of the tables that
too in Random (ie, the 10 tables transferred need not
be same in subsequent runs). Is there a limitation on the number of tasks
that can run in Parellel ?

Thanks,
rgn

Not that I know of and you can alter the figure on your package properties but be aware that although things may look to be going in
parallel they may actually just be scheduling in and out of the processor (Context Switching) very quickly.

This will happen if you are trying to stack more processes to go in parallel than you have processors.

--

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


"Gopinath R" <gopinathr (AT) healthasyst (DOT) com> wrote

In the package properties is an option to "Limit the maximum number of tasks
executed in parallel to:" box. You can configure it there. However,
setting it too high will slow down your process instead of speeding it up.
You will need to run tests to see what value is best for your server
configuration.

Scott

"Gopinath R" <gopinathr (AT) healthasyst (DOT) com> wrote

Hello Allen,

Before the sending the mail yesterday, I did increase the number of Tasks
executed in Parallel to 20 in the DTS Package Properties window.
However, it still does populate only 10 tables (check my originial post in
the end of this mail).

Apart from that error, we also errors generated randomly. In the DTS
package, we have a "Dynamic Properties Task" that sets some global
variables. All
of a sudden we seem to be getting quite a few errors and here are some of
them. I mean errors found in the line containing "'General Timeout'", "'Data
Source Name'", "'Read-Only Data Source'", "'Pass By Ref Accessors'",
"'Catalog Location'","'Column Definition'" ... etc

Would you know why it would through these errors ?

Gopi

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description1:CriticalMass) SubStep
'DTSStep_DTSActiveScriptTask_5' failed with the following error: Error
Code: 0 Error Source= Microsoft OLE DB Service Components Error
Description: Format of the initialization string does not conform to the OLE
DB specification. Error on Line 12 (Microsoft OLE DB Service Components
(80040e73): Format of the initialization string does not conform to the OLE
DB specification.) Package failed because Step
'DTSStep_DTSActiveScriptTask_5' failed. Step Error code: 80040428 Step
Error Help File:sqldts80.hlp Step Error Help Context ID:1100
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description1:Favorites) SubStep 'DTSStep_DTSDataPumpTask_10'
failed with the following error: OleDBProperty 'General Timeout' already
exists in the collection. Package failed because Step
'DTSStep_DTSDataPumpTask_10' failed. Step Error code: 80040428 Step Error
Help File:sqldts80.hlp Step Error Help Context ID:1100
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:OleDBProperty 'General Timeout' already exists in the
collection. Step Error code: 800403EC Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:OleDBProperty 'Data Source Name' already exists in
the collection. Step Error code: 800403EC Step Error Help
File:sqldts80.hlp Step Error Help Context ID:1100
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description1:Favorites) SubStep
'DTSStep_DTSDynamicPropertiesTask_1' failed with the following error:
OleDBProperty 'Read-Only Data Source' already exists in the collection.
Package failed because Step 'DTSStep_DTSDynamicPropertiesTask_1' failed.
Step Error code: 80040428 Step Error Help File:sqldts80.hlp Step Error
Help Context ID:1100
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description1:Favorites) SubStep
'DTSStep_DTSDynamicPropertiesTask_1' failed with the following error:
OleDBProperty 'Pass By Ref Accessors' already exists in the collection.
Package failed because Step 'DTSStep_DTSDynamicPropertiesTask_1' failed.
Step Error code: 80040428 Step Error Help File:sqldts80.hlp Step Error
Help Context ID:1100
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description1:Favorites) SubStep 'DTSStep_DTSDataPumpTask_10'
failed with the following error: OleDBProperty 'Current Language' already
exists in the collection. Package failed because Step
'DTSStep_DTSDataPumpTask_10' failed. Step Error code: 80040428 Step Error
Help File:sqldts80.hlp Step Error Help Context ID:1100
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description1:Favorites) SubStep
'DTSStep_DTSDynamicPropertiesTask_1' failed with the following error:
OleDBProperty 'Catalog Location' already exists in the collection. Package
failed because Step 'DTSStep_DTSDynamicPropertiesTask_1' failed. Step Error
code: 80040428 Step Error Help File:sqldts80.hlp Step Error Help Context
ID:1100
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:OleDBProperty 'NULL Concatenation Behavior' already
exists in the collection. Step Error code: 800403EC Step Error Help
File:sqldts80.hlp Step Error Help Context ID:1100
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description1:Favorites) SubStep
'DTSStep_DTSDynamicPropertiesTask_1' failed with the following error:
OleDBProperty 'Column Definition' already exists in the collection. Package
failed because Step 'DTSStep_DTSDynamicPropertiesTask_1' failed. Step Error
code: 80040428 Step Error Help File:sqldts80.hlp Step Error Help Context
ID:1100
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:OleDBProperty 'General Timeout' already exists in the
collection. Step Error code: 800403EC Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

We have two DTS packages :

[A] Wrapper.dts
(1) Gets the connection details, ie, SQLServer name, Uid,
Password and the Database, for a client server
from a table. The task, named as "GetNextClient" is implemented via the
"Exec SQL Task"
(2) Calls the LoadContracts.dts package and passes over the
variables


[b] LoadContracts.dts
(1) Accepts the Variables from Wrapper.dts via "Dynamic Task
Properties"
(2) Pulls the records as defined in the "Transform Data Task"
(3) Loads the records into the target database.


Since all the errors generated are in way or the other related to
Description:OleDBProperty, I moved the "GetNextClient" SQL
task from Wrapper.dts to LoadContract.dts and it seems to be working fine
though I dont understand why it should give the
errors in the first place.

Has anyone encountered simillar errors ? But we still have not figured out
the problem with Parellel tasks.

Thanks,
rgn



"Gopinath R" <gopinathr (AT) healthasyst (DOT) com> wrote

Like i said yesterday I know of no restriction internally to DTS and setting the "Eecute in Parallel" option on the package will let
you set more than that so I really do think that this will be resource bound.

If you have a 4 proc box and as for 8 tasks to go at once then in a very simplistic way the processes would have to be scheduled in
and out of the processor and 2 per processor providing it was the only thing on the box. The more tasks you ask to go in parallel
the harder it will be for the resource to keep up.



--

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


"Gopinath R" <gopinathr (AT) healthasyst (DOT) com> wrote