DTS and Stored Procedure

[TwoOdd]

I created a DTS package to import an Excel Spreadsheet to SQL Server. The package runs fine by itself, but when I try to call it from a stored procedure, I get an error saying that the path to the file is not valid. The file is not located on the server.

Any help would be appreciated.

Thanks,

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[JayKusch]

How are you making your call. By a JobName or use of DTSRun?

Thanks

J. Kusch

 

[TwoOdd]

DTSRun.

I tried it with a file on the local machine and it worked. It doesn't like going to a mapped drive. Why?

Thanks,

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[wcburton]

Does the account used to run SQL Server have access rights to the network or only local machine rights?

 

[TwoOdd]

Network. The package runs fine if I right click on the package and choose Execute Package. It has a problem when I run it from the stored procedure.

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[wcburton]

I got tied up with other projects - have you figured out the problem?

2 things to clarify--

First:
When you click on it and run it, it runs as your security - not that of the SQL Server or SQL Server Agent. When you run it from the stored proc it runs as the account used to start SQL Server which is often a local machine account.

Second:
Another context on the same machine does not necessarily have access to mapped drives. Have you tried using the UNC path to the file instead of the mapped drive?

Please post back and let me know if it was one of these or something else.

 

[NathanGriffiths]

Might be related - I had a problem with Excel spreadsheets where the file path was longer than 130 characters.

see thread183-443608

 

[TwoOdd]

I ended up creating a work around. First, I created a network drive:

set objNet=Server.CreateObject("WScript.Network")
objNet.MapNetworkDrive Drive Letter, Shared Drive, UpdateProfile(True/False), UserName, Password

Then I used the objFileSys.CopyFile command to copy the file from the shared drive to the local drive. Once the file was on the local drive, I was able to run the stored procedure without any problems.

If anyone figures out how to do this without having to copy the file over, that would be helpful. Until then, at least I have a workaround.

Thanks for all the help,


TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner