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 :o 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.I am betting the user context that the dts package is executing under dopes not have access to the UNC path.|||I do not understand that you are saying
"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."

To create a schedule job you must be sa and DTS use SQL-Agent login to execute. What you need to do is give the SQL-Agent login the permission to access the target location \\servername\foldername\. What I mean is you do not ever pass password in DTS package to make a connection to Excel document. Please clrify.

Thanks|||Is your windows login having appropriate rights on that folder & files..?

To check try this...

1. Click Start Menu -> Run
2. Write \\servername\foldername\filename.xls -> Press OK

If the file opens, ensure that you are having appropriate rights & need to check something else.|||I do not understand that you are saying
"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."

To create a schedule job you must be sa and DTS use SQL-Agent login to execute. What you need to do is give the SQL-Agent login the permission to access the target location \\servername\foldername\. What I mean is you do not ever pass password in DTS package to make a connection to Excel document. Please clrify.

Thanks

Thanks for all replies.

To clarify... A scheduled job that I created runs steps with TSQL command. I have a stored procedure to load and execute DTS package by database account. Since more and more securities are concerned, it is not possible for me to run the scheduled job with DTSRUN.exe via local windows account.

For more information: http://www.databasejournal.com/features/mssql/article.php/10894_1459181_3

That's why I can't identify what the SQL-Agent login is because I didn't use it to run the jobs.|||OK
You mean you are using the following sp inside the job:
either
1. using integrated security: EXEC spExecutePKGGlobalVariables @.Server='MyServer',
@.PkgName='MyPackage', @.IntSecurity=1
or
2. using the current connection via SUSER_NAME() EXEC spExecutePKGGlobalVariables @.Server='MyServer',
@.PkgName='MyPackage', @.ServerPWD='xxxx', @.PkgPWD='xxxx'

But when a job is executed, specially for file access which is your case, SQL uses SQL-Agent NT login to access the file not the user login or sql login. Therfore you must know what the SQL-Agent login is and check if the SQL-Agent login has the access permission. We had lots of issues before accessing
Excel files in the network drive. Later on we found the issue came from the permission of SQL-Agent login.
So I suggest
1. You contact your SQL server administrator to inform you SQL-Agent NT login and request permission to access the file. - Best solution.
Or
2. You may create a macro in the Excel to import the data. - Hard to schedule. But I can give you hand for this.
Or
3. You may create vbscript to import the data into Excel. This, you may be able to schedule to run. Similar to #2.