Any limit on the number of tasks in a DTS job?

Last Post 26 Apr 2007 04:57 AM by jagsandhu. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
mathewbiju
New Member
New Member

--
26 Apr 2007 01:44 AM
Hello,
we are running SQL 2000 Enterprise Edition.
We had a DTS job which had around 31 tasks in it.
Each task executes a storeproc which extracts data from a table.

We added 4 new tables to the DB hence this DTS job had to be amended to add 4 new tasks to extract data from the new tables. Once we applied this change , we started getting this error:

Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
Event ID: 318
Date: 18/04/2007
Time: 06:00:21
User: N/A
Computer: SDCNTRTSSQL01
Description:
Unable to read local eventlog (reason: The data area passed to a system call is too small).

While investigating, i found that if i remove any 1 task from the DTS job, it starts working fine!!
So the question is: Is there any limit on the number of tasks in a DTS job?
If yes, is it configurable?
Please help.

Thanks
Regards,
Biju


mathewbiju
New Member
New Member

--
26 Apr 2007 04:33 AM
thanks olu,
actually the dts job is run via a SQL Agent job.
we use only Execute SQL tasks in it. There are 35 of them.

The entry in the event log does not suggest any error. I have attached below. After this the error entry appears.

one more question: where do we configure the max number of errors before failure occurs?
Kindly help
Regards,
Biju


The execution of the following DTS Package succeeded:

Package Name: DTS_Test_Extract_Files
Package Description: (null)
Package ID: {2186F4E3-E69A-46F5-B59A-EBE6C6159BC1}
Package Version: {1E6FF92E-8538-4BF1-B9F9-E9207509CED8}
Package Execution Lineage: {5419BCF3-D184-4C03-913F-5B9A7D3AA1D6}
Executed On: Server1
Executed By: svc_sql
Execution Started: 18/04/2007 07:00:31
Execution Completed: 18/04/2007 07:00:34
Total Execution Time: 3.187 seconds

Package Steps execution information:


Step 'DTSStep_DTSActiveScriptTask_1' succeeded
Step Execution Started: 18/04/2007 07:00:31
Step Execution Completed: 18/04/2007 07:00:31
Total Step Execution Time: 0.015 seconds
Progress count in Step: 0

Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 18/04/2007 07:00:31
Step Execution Completed: 18/04/2007 07:00:32
Total Step Execution Time: 0.313 seconds
Progress count in Step: 0

Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
Step Execution Started: 18/04/2007 07:00:31
Step Execution Completed: 18/04/2007 07:00:32
Total Step Execution Time: 0.438 seconds
Progress count in Step: 0

Step 'DTSStep_DTSExecuteSQLTask_3' succeeded
Step Execution Started: 18/04/2007 07:00:31
Step Execution Completed: 18/04/2007 07:00:32
Total Step Execution Time: 0.406 seconds
Progress count in Step: 0

Step 'DTSStep_DTSActiveScriptTask_2' succeeded
Step Execution Started: 18/04/2007 07:00:34
Step Execution Completed: 18/04/2007 07:00:34
Total Step Execution Time: 0 seconds
Progress count in Step: 0

Step 'DTSStep_DTSExecuteSQLTask_4' succeeded
Step Execution Started: 18/04/2007 07:00:31
Step Execution Completed: 18/04/2007 07:00:32
Total Step Execution Time: 0.391 seconds
Progress count in Step: 0

Step 'DTSStep_DTSExecuteSQLTask_5' succeeded
Step Execution Started: 18/04/2007 07:00:32
Step Execution Completed: 18/04/2007 07:00:32
Total Step Execution Time: 0.343 seconds
Progress count in Step: 0
.....
;;;;;;

Step 'DTSStep_DTSExecuteSQLTask_35' succeeded
Step Execution Started: 18/04/2007 07:00:34
Step Execution Completed: 18/04/2007 07:00:34
Total Step Execution Time: 0.25 seconds
Progress count in Step: 0

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.




jagsandhu
New Member
New Member

--
26 Apr 2007 04:57 AM
Hi Biju,

You can specify max errors in the OPTIONS tab on TRANSFORM DATA TASK property.

You can get there by right clicking the transform task and choosing properties.

Jag
mathewbiju
New Member
New Member

--
26 Apr 2007 05:20 AM
thanks jag

olu,
in the Sql Agent we call an storedproc: exec stor_proc1

Within that sp we have a code like this


select @Cmd = 'dtsrun /S' + @@servername + ' /E /WTrue /NDTS_Test_Extract_Files'
exec master..xp_cmdshell @Cmd, no_output

Please let me know if anything strikes you.
thanks

Regards,
biju
mathewbiju
New Member
New Member

--
26 Apr 2007 07:13 AM
Hi Olu,
It works fine.thanks
but now the issue i have is i do not have any notification if the DTS fails!

Regards,
Biju

mathewbiju
New Member
New Member

--
26 Apr 2007 08:18 PM
thanks olu, russell

regards,
biju
You are not authorized to post a reply.

Acceptable Use Policy