|
|
You're in the
sql server dts
group:Is there a limitation on the number of Tasks that can Run in Parellel ?
sql server dts:
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 [quoted text, click to view] "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 >
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 [quoted text, click to view] "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 >
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
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 [quoted text, click to view] "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 >> > >
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 [quoted text, click to view] "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)
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 [quoted text, click to view] "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
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |