DTS package runs well on EM but not in sp

Question

DTS package runs well on EM but not in sp

Eduardo Olivera

SSCommitted

Points: 1829
More actions
November 14, 2007 at 9:29 am

#376981

I have a DTS package that I developed on SQL 2000 to support an external Fox based application that ports data to SQL. This package inserts the fox data into temp tables on SQL and the moves it to the live tables. When I run this package on the Enterprise Manager console, it runs flawlessly but, if I try running it from a stored procedure that I need the Fox application to run for the transfer, it complains about not finding the file path. The Fox programmers tell me that they don't get any error back but I did when I ran the stored procedure on Query Analyzer. I originally had the drive mapped as it is on the local server but then changed it to its full path to see if it made a difference but it's still the same: it runs outside the stored procedure but it does not find the file path inside it.
Thanks in advance,
Ed

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 1

This type of problem is usually caused by either mapping or permissions.

When you run the DTS package through EM it runs from your local machine so any drive mappings are based on your personal mappings on your PC. When you run it from within a stored procedure it runs from the server using the server mappings.

If you are using a full UNC file path (\servername\folder\...) then the problem will not be mappings (ans the UNC path will be the same from both your PC and from the server) and is probably permissions. Can you check that the SQL Server account has permissions to access the file?

Jez

Eduardo Olivera SSCommitted Points: 1829 More actions · Answer 2

Hi Jez,

In that case, since the stored procedure is called by a sql login it takes the security context of the caller or none at all since it is not mapped to a windows login, right? I am going to try having the user call DTSRun from his workstation, which I believe is mapped and has permissions to the directory where the data is. Unfortunately, he took a couple of days off and won't be back until Monday.

Thanks,

Ed

Eduardo Olivera SSCommitted Points: 1829 More actions · Answer 3

It now runs making a command call to dtsrun from the computer where the outside application runs. Since that computer has access to the path, it runs flawlessly.

Thanks,

Ed