Re: Is there a limitation on the number of Tasks that can Run in Parellel ?

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 01/06/05 

Date: Thu, 6 Jan 2005 22:31:09 -0000

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@healthasyst.com> wrote in message news:%23EWigFA9EHA.368@TK2MSFTNGP10.phx.gbl...
> 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@healthasyst.com> wrote in message news:O7792n78EHA.3376@TK2MSFTNGP12.phx.gbl...
>> 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 Description:(1: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 Description:(1: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 Description:(1: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 Description:(1: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 Description:(1: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 Description:(1: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 Description:(1: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@no-spam.sqldts.com> wrote in message news:%23jXeh218EHA.3616@TK2MSFTNGP11.phx.gbl...
>>> 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@healthasyst.com> wrote in message news:OXPxUM08EHA.3124@TK2MSFTNGP11.phx.gbl...
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>

Relevant Pages

  • Re: Is there a limitation on the number of Tasks that can Run in Parellel ?
    ... executed in Parallel to 20 in the DTS Package Properties window. ... Error Help File:sqldts80.hlp Step Error Help Context ID:1100 ... Step Error Source: Microsoft Data Transformation Services Package ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS Process Cube Task
    ... It has a command-line interface -- so you would use a DTS Process Task ... private dimension that contained more than 2 million members. ... occasionally the cube would process, and that was throwing me off. ... DTSRUN of the package from the command-line. ...
    (microsoft.public.sqlserver.olap)
  • RE: DTS Hanged When Scheduled
    ... Please feel free to respond to the newsgroups if you need any ... Microsoft Online Support ... | Subject: RE: DTS Hanged When Scheduled ... this behavior occurs because the package is using ...
    (microsoft.public.sqlserver.dts)
  • Re: Import and Schedule DTS on MSDE
    ... The dtsrun utility executes a package created using Data Transformation Services. ... Displays the command prompt options. ... Displays the encrypted command used to execute the DTS package without executing it. ...
    (microsoft.public.sqlserver.tools)
  • Re: Is there a limitation on the number of Tasks that can Run in Parellel ?
    ... > executed in Parallel to 20 in the DTS Package Properties window. ... > Step Error Source: Microsoft Data Transformation Services Package ...
    (microsoft.public.sqlserver.dts)
Loading