Connecting/Importing from a remote csv file using UNC connection

[mitchelt]

Hi,

I created a DTS package with a connection similar to this example using a bogus IP:

FILE: \\10.15.1.201\c$\websites\localuser\sitename\foldername\filename.csv

The package is on our SQL Server Box and the file is on a different server...our web server.

If I run the DTS package manually it works fine, the records are imported.

When I schedule the package to run automatically it fails on step one which is the connection.

Any clues as to why this is happening?

Thanks!

Mitch

 

[mrdenny]

The Windows account that the SQL Server is running under probably doesn't have access to the web server. The Windows account that the SQL Server is running as will need admin rights to the web server in order to connect to the file path that you have shown.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mitchelt]

Thanks.

What's I don't understand is that it works if I manually execute it from Enterprise Manager (DTS\local packages)

But fails if scheduled.

I should also mention that I am doing all of this via remote desktop, not my local computer.

Mitch

 

[mrdenny]

When you run it in Enterprise Manager it will be running via what ever account you are logged into the SQL Server as, not as the SQL Account.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mitchelt]

Denny,

Thanks for the tips.

Do you know by chance which Windows Account SQL uses when running a DTS package?

Thanks!

Mitch

 

[mrdenny]

It uses what ever account the SQL Agent service is running under. In most places that will be the same account the SQL Server is running under. It could be any valid domain or local account. If it's a local account you'll need to setup a domain account and use the domain account.

You can see what account it's running under by looking at the MSSQLSERVER service in the Computer Manager Console.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mitchelt]

Denny,

Sorry for the delayed response...thanks for the info.

I decided that I did not want to mess with the security settings seeing that we are trying lock stuff down and be more secure so I just setup the DTS to FTP IN the file, import it then delete the file from the local and remote computer.

Thanks,

Mitch