DTS Package Excel Connection - Please Advise

Hi,

I'm using MS SQL 2000 and I want to export data from MS SQL database to MS Excel file. So, I choose the DTS package tool to do this job.

I try to create the connection to the Excel file, which located in the shared location by using the UNC path, e.g. \\servername\foldername\filename.xls However, I cannot provide the username/password to connect to the file's location. So, the connection does not work.

Do you have any idea how to create the Excel connection along with shared username/password ?

Anyway, if it is not possible :-( any advices about exporting MS SQL data to MS Excel sheet via DTS packages are still welcome....

(The reason that I prefer DTS package bec. I can schedule the job to run it automatically)

Thank you in advance.

You need not mention the user and password to the excel file, until unless you want to secure the access to excel file with a password.

KBA http://support.microsoft.com/kb/319951 for your information to perform this action without any issues.

|||

Thanks, I have tested by sharing a folder on my machine and give access to "Everyone" account. It works fine .

However, with the real situation. The shared folder, which I have to place files in it, needs authentication. They don't allow shared folder to have a read/write access for "Everyone" account.

What should I do?

|||With a READ permission those users should be able to read the file on that shared folder, this has to be done using Windows explorer and SQL will not have any influence in this case. But if you are running this as a scheduled job then SQLAGent will do the rest if it has required privileges.