DTS Package fails

Question

DTS Package fails

abdul-256523

SSC Veteran

Points: 281
More actions
February 2, 2006 at 4:43 am

#90530

Hi Sudeep,
We have DTS package which runs evey hour to transfer data from access (.mdb) file to sql server databases, just some time back it was working fine.. but now if fails.. below is the error message from view job history... DTSRun: Executing... DTSRun OnStart: Copy Data from ohReport to[BNW].[dbo].[ohReport] Step DTSRun OnError: Copy Data from ohReport to [BNW].[dbo].[ohReport] Step, Error = -2147008507 (80074005) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 700 Error Detail Records: Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file:
sqldts.hlp Help context: 700 Error: -2147467259 (80004005);
Provider Error: -66913278 (FC02FC02) Error string: Disk or network error. Error source: Microsoft JET Database Engine Help file: Help context: 5003043 DTSRun OnFinish: Copy Data from ohReport to [BNW].[dbo].[ohReport] Step DTSRun OnS... Process Exit Code 12. The step failed.
When i try to run the package manually it runs fine but when i schedule it fails
I tried pinging to the server where .mdb file is there its replying.

TIA
abdul

Viewing 12 posts - 1 through 11 (of 11 total)

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

Giles McArdell SSC Eights! Points: 875 More actions · Answer 1

Ooooh this sounds familiar, bells going off all over the place, I`m sure Ive had this same error

Its the bit 'When i try to run the package manually it runs fine but when i schedule it fails' that sounds familiar, I think it has to do with the timing of events in the DTS, IE one part of the DTS still has the file open, or has not released it (or the system has not woken up to the fact that its finished with the file) when another step tries to open it.

Run manually it runs a bit slower, or in a different server context, and all is OK.

You could try putting in a short pause before the offending step.

Another thought that occurs to me is that the permissions of the scheduler may not allow it to access the file.

Finally - remember that DTS`s run from the location where you initiate them (IE not necessarily on the server itself). The server may have different paths (mapped drives etc) to a different PC on the network, and so a DTS that works fine on one machine may fail on another if it cannot find the required paths.

Hope this helps

"Don`t try to engage my enthusiasm, I don`t have one."

(Marvin)

Jim Dillon-291542 Hall of Fame Points: 3344 More actions · Answer 2

You might also make sure that you are using UNC paths and not mapped drives. I had the same problem when I was using a mapped drive for a dts package instead of a full unc path. The job would run when executed manually, but it would fail when scheduled. It was explained to me that the sql server agent had no idea what to do with a mapped drive. I change the path from using a mapped drive to a full unc path and the job has worked since.

Thanks,

jim

Giles McArdell SSC Eights! Points: 875 More actions · Answer 3

If all the mappings are the same (IE if H:, or whatever, always evaluates to the same full path on all the PCs) then neither server agent nor DTSs should have a problem with paths.

"Don`t try to engage my enthusiasm, I don`t have one."

(Marvin)

Kathy-82899 Right there with Babe Points: 762 More actions · Answer 4

We had a similar problem with a DTS package that could be run manually but was not running when scheduled. Problem turned out to be a space issue on the C: drive of the server. Some backup software had failed and left a huge cache file on the server. Once that was cleared up everything starting working again.

Michael Lee-169622 SSCommitted Points: 1721 More actions · Answer 5

Mapped drives only work when a user is logged in to the system (server, workstation, whatever). DTS runs under the SQL Server Agent SERVICE, which does not use any logged in user. UNC is the only way to connect if the Access DB is on another system.

Shalom!,

Michael Lee

Kathy-82899 Right there with Babe Points: 762 More actions · Answer 6

Don't forget, Abdul said it was working then stopped. He never said he was using mapped drives. UNC is the correct way to connect but if the scheduled job was working and then stopped working that is probably not what is causing his job to fail now.

Michael Lee-169622 SSCommitted Points: 1721 More actions · Answer 7

Agreed. I was just addressing the mapped drive mis-information. Abdul should check permissions, although the error does not seem to indicate that problem.

I would say that almost 100% of the time when I have a DTS Job that runs manually and dosn't run when scheduled, it has to do with permissions.

Someone may have changed a PW or permission for the SQL Server Agent's run account that prohibits the access of the Access file.

Shalom!,

Michael Lee

Michael E. Baker Say Hey Kid Points: 676 More actions · Answer 8

You are getting a "Disk or Network Error". You might want to check for file existance before the transform.

Try an ActiveX Script Task before the transform. You can use the following code. Note that the CheckFile() function uses a global variable named "FileName":

Function Main()

DTSGlobalVariables("FileName").Value = ""

If Not CheckFile() Then

Main = DTSTaskExecResult_Failure

Exit Function

End If

Main = DTSTaskExecResult_Success

Set oPackage = Nothing

End Function

Function CheckFile()

dim strFileName,fso

Set fso = CreateObject("Scripting.FileSystemObject" )

strFileName = DTSGlobalVariables("FileName").Value

If fso.FileExists(strFileName) then

Set fso = Nothing

CheckFile = True

Else

Set fso = Nothing

CheckFile = False

End If

End Function

abdul-256523 SSC Veteran Points: 281 More actions · Answer 9

Hi All,

Thank you everyone for your suggestion and sorry to be late for repying, yes as said by Lee it was a problem with permissions some one had changed due to which it stopped working.

Thanks again

Regards

Abdul

Russbbass SSC-Addicted Points: 462 More actions · Answer 10

I am having the same error, but my issue is a bit different. Wew have a mixed enviroment, some SS2000 and SS2005. We have deployed the SS2005 management studio to all the DBA's machines and it is running with the SS2000 client tools still installed.

Using a machine that has both installed, a DTS package is checked out of VSS (stored as a SSF) and saved onto a SS2000, SP4 server. I then try and execute the package on the server (which only has SS2000) via DTSRUN and it fails with the dreaded "unspecified error". If I run it remotely via the DBA machine (dtsrun from command line), it executes just fine.

This leads me to believe that there is an issue deploying DTS packages to a SS2000 machine using a client machine that has both clients.

Has anyone run into this and if so, any work arounds, other than using a seperate machine for deployment?

Thanks in advance for the help/comments.

Russ

DBA, because they haven't developed a 12 step program for it yet.

Russ
"I'm a DBA because they haven't developed a 12 step program yet."

Russbbass SSC-Addicted Points: 462 More actions · Answer 11