sql server dts: I have a dot net application in c# which invokes a stored procedure.
The stored procedure invokes a DTS package . DTS package take a text file and transfer data to sql server table
This part fails roughly 3 times in 100 attempt.
I am not able to find out why exactly it is happening.
When I web application runs the code again with same parameter it always suceeds.
I will appreciate any insinght.
The part of code for stored proc and dts error log message is given below

DTS error message in short is-
Step Error Description:The process cannot access the file because it is being used by another process.
(Microsoft Data Transformation Services (DTS) Package (80070020): Cannot open a log file of specified name. The process cannot access the file because it is being used by another process.
)


Stored proc code-

-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
--PRINT '*** Execute failed'
EXEC sp_OAGetErrorInfo @oPKG, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @ErrorInsertStmt=@ErrorInsertStmt +''''+Replace(@src,'''',' ')+''','''+Replace(@desc,'''',' ')+''','
SET @ErrorInsertStmt=@ErrorInsertStmt +'''Package execution failed in sp_OAMethod '')'
EXEC sp_executesql @ErrorInsertStmt
select @dtsReturnValue=4
RETURN
END


the above code fails and the dts error log says

****************************************************************************************************
The execution of the following DTS Package failed:

Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700


Package Name: pkg_cr_tally
Package Description: (null)
Package ID: {09192115-BD41-4080-ABDC-ECF003736C0B}
Package Version: {DB783FFC-A11B-420E-A950-5F9AD4231193}
Package Execution Lineage: {9EB6AA93-B1F0-45B0-82AD-333B21F6A0AA}
Executed On: GANDALF
Executed By: sqlservice
Execution Started: 1/14/2004 1:31:05 PM
Execution Completed: 1/14/2004 1:31:06 PM
Total Execution Time: 0.297 seconds

Package Steps execution information:


Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The process cannot access the file because it is being used by another process.
(Microsoft Data Transformation Services (DTS) Package (80070020): Cannot open a log file of specified name. The process cannot access the file because it is being used by another process.
)
Step Error code: 80070020
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

Step Execution Started: 1/14/2004 1:31:05 PM
Step Execution Completed: 1/14/2004 1:31:06 PM
Total Step Execution Time: 0.266 seconds
Progress count in Step: 0

Step 'DTSStep_DTSDynamicPropertiesTask_1' succeeded
Step Execution Started: 1/14/2004 1:31:05 PM
Step Execution Completed: 1/14/2004 1:31:05 PM
Total Step Execution Time: 0 seconds
Progress count in Step: 0
****************************************************************************************************

It looks as though a process is using the text file that you want to use i
the DataPump task
Maybe the connection os still open
Try setting "Close Connection on Completion" in the workflow properties
[quoted text, click to view]

Remember that DTS is a client and filepaths need to be as per the clien
view
[quoted text, click to view]

Are multiple tasks executing at the same time therefore logging to the sam
file
[quoted text, click to view]

Have you tried saving the package out to another name and then using that.
[quoted text, click to view]

Though One question I have for you
there is a property "Limit the maximum number of tasks executed in parallel to
ours is web application and at one point of time there could be any number of user accessing the application
Currently it is set to 4( default)
what will happen if more there is more then 4 simultanious request
What should be ideal value for real time web application which in turn accesses the DTS package by invoking a stored procedure
Could this default value of 4 be cause of this erro

Thanks for all hel
ra

It looks as though a process is using the text file that you want to use in
the DataPump task.
Maybe the connection os still open?
Try setting "Close Connection on Completion" in the workflow properties.

Remember that DTS is a client and filepaths need to be as per the client
view.

Are multiple tasks executing at the same time therefore logging to the same
file?

Have you tried saving the package out to another name and then using that.?


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


[quoted text, click to view]
open a log file of specified name. The process cannot access the file
because it is being used by another process.
[quoted text, click to view]
****************************************************************************
************************
[quoted text, click to view]
open a log file of specified name. The process cannot access the file
because it is being used by another process.
[quoted text, click to view]
****************************************************************************
************************
[quoted text, click to view]

All that setting means is that DTS can start to execute 4 tasks in your
package to go at the same time. I usually set this this to "Count of
processors in box" as you can see context switching"


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


[quoted text, click to view]
success > I open the text file based on global variable assigned in previous
step, then transform data task step which uses a few activex script and
normal mapping from text file to destination database. even when I disable
database logging and error logging to text file this problem happens
[quoted text, click to view]