DTS Error --- Error string: The file exists

Question

Post reply

DTS Error --- Error string: The file exists

Rajarshi Mukherjee

SSC-Addicted

Points: 442
More actions
April 1, 2009 at 8:06 am

#219594

I have developed a DTS job which is running fine if execute it by right clicking on it. But whenever I schedule it as a job to be run later, I am getting the below error message.
[font="Arial"]Executed as user: IUSER\BTIS0402. DTSRun: Loading... Error: -2147024816 (80070050); Provider Error: 0 (0) Error string: The file exists. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713. Process Exit Code 1. The step failed.[/font]
What could be the possible reason for this? I searched a lot in web but in vain

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 1

Maybe you could provide some information about what the package does? Where the error might be.

If you have a file exists, have you checked for that?

The execution on the server takes place in the context of the server, so the SQL Agent account, it's drive mappings, etc. When you run it from your workstation, it's running from there, not the server.

Rajarshi Mukherjee SSC-Addicted Points: 442 More actions · Answer 2

Please find the attached pic of the DTS.

Functionality -

----------------

1) Truncate all the temporary tables at starting point of the execution

2) Source file path is given of the CSV file which will be received every after 5 mins from interface system. File will be moved once loading is finished so file path directory is always empty when the DTS is being executed.

3) File will be loaded in in database using transformation task

4) Once file is loaded P_VDMT_EXTRACT stored procedure will be executed which is responsible for some data manipulation and population of output extract table.

5) If the stored procedure throws any error then error output extract file will be generated ( LEFT SIDE ) otherwise output extract file will be generated taking data from output extract table via transformation task (RIGHT SIDE).

Now I am getting the error in step 1 itself.

Rajarshi Mukherjee SSC-Addicted Points: 442 More actions · Answer 3

FYI This DTS jobs are running fine earlier. But few days back the SQL agent account has been changed and stopped working and showing the mentioned error message. Not sure how this is possible !!!

I then chased the DBA guy and but as per him, the SQL agent a/c has sufficient permission to access the drive from where the input CSV file is being picked up for loading.

What could the other reasons for this error message. Surprisingly I couldn't find a single entry in google regading this error type. Am I the first person who is facing such problems???

Rajarshi Mukherjee SSC-Addicted Points: 442 More actions · Answer 4

Rajarshi Mukherjee

SSC-Addicted

Points: 442

April 6, 2009 at 10:05 am

#972684

Dose anyone has any answer ?

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 5

Did you try logging in to the network as the SQL Server Agent account (may need to involve your DBA), right clicking the job, and see if it runs? If it does, than it is not a permission issue, and may be a problem similar to what I have seen (where a scheduled job "loses" the proxy user it was trying to run as, and instead runs as anonymous). If it doesn't, then your DBA is mistaken and the account does not have sufficient permissions.

surendrababu.p Grasshopper Points: 22 More actions · Answer 6

Hi,

I am the second person who faced similar issue with DTS pacakge with the same error message.

===========================

Execution failed with the following error: "ERROR : errorCode=-2147024816 description=The file exists.

helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".

===========================

I am running this using shell script.

I also had extensive search ion web but no use.

Any thoughts on how to solve this?

Any help or info is really really appreciated.

Thanks in advance

surendrababu.p Grasshopper Points: 22 More actions · Answer 7

Does any one have any details on this? please help in resoving this?

Thanks in advance

Rajarshi Mukherjee SSC-Addicted Points: 442 More actions · Answer 8

Hi Timothy,

I have checked with the help of DBA for both SQL Agent a/c & my personal a/c. DTS is running fine whenever i am running it manually by clicking "Execute" option. But getting error when i am scheduling it as a job

See the error message --

The job failed. The Job was invoked by User IUSER\602336842. The last step to run was step 1 (ISDN30 - VDMT Extract Generation 1 2).

Executed as user: IUSER\BTIS0402. DTSRun: Loading... Error: -2147024816 (80070050); Provider Error: 0 (0) Error string: The file exists. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713. Process Exit Code 1. The step failed.

What could be the possible for this?

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 9

Can you provide some additional detail as to the steps within your DTS package?

Rajarshi Mukherjee SSC-Addicted Points: 442 More actions · Answer 10

Please read the post no. 3 from top where I have mentioned the functionality in detail. An image of DTS is also attached.

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 11

Sorry, I missed that.

A couple of questions:

1) how do you know that the file is finished transfering when you start loading the data?

2) Can you provide the source you are using for "Move Parameter File"?

Thanks!

Rajarshi Mukherjee SSC-Addicted Points: 442 More actions · Answer 12

1) Could be a issue. but input parameter file size is very small (2-4KB Max). Also got the error when job is scheduled well after the file is transferred ( like 5 min later)

2) Code for Move parameter file --

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

Dim fso, f

Dim ts

ts=Day(Date)&Month(Date)&Year(Date)&Hour(time)&Minute(time)&Second(time)

DTSGlobalVariables("Filepath").Value="\\iuser\\intranet\webapplicationsdev.nat.bt.com\isdn30\data_files\vdmt\INFILE\INFILE_IETPAT_DQ1.CSV"

DTSGlobalVariables("MoveFilePath").Value="\\iuser\\intranet\webapplicationsdev.nat.bt.com\isdn30\data_files\vdmt\ARCHIVE\INFILE_IETPAT_DQ1_"&ts&".CSV"

Set fso = CreateObject("Scripting.FileSystemObject")

Set f = fso.GetFile(DTSGlobalVariables("Filepath").Value)

f.move(DTSGlobalVariables("MoveFilePath").Value)

fso.deletefile(DTSGlobalVariables("Filepath").Value )

Main = DTSTaskExecResult_Success

End Function

Rajarshi Mukherjee SSC-Addicted Points: 442 More actions · Answer 13

Getting the error in step 2 itself if you look at the picture when input file is getting loaded using tranformation task. So I assume problem is not related to MOVE PARAMETER FILE Task.

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 14

I apologize, but it has been a while since I have worked with DTS. I would recommend breaking up this package into sperate packages or job steps, creating the simplest steps possible given your objective. I would use a "MOVE" system command instead of a package to perform the move. Then I would try them one at a time and attempt to isolate the problem. Once you have it isolated you can implement a work around (such as BCP or even OpenQuery).

I am sorry I could not help you find a solution to the actual problem!