Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2007
    Posts
    2

    Unanswered: 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.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am betting the user context that the dts package is executing under dopes not have access to the UNC path.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2007
    Posts
    38
    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

  4. #4
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    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.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  5. #5
    Join Date
    Feb 2007
    Posts
    2
    Quote Originally Posted by TerryP
    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/featu...0894_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.

  6. #6
    Join Date
    Feb 2007
    Posts
    38
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •