Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Posts
    2

    Unanswered: Problem using DTSRUN in stored procedure / query analyzer

    Hi. I am having a problem using DTSRun in a stored procedure or in query analyzer.
    My DTS package truncates a table and then imports data from an Excel spreadsheet into the table.
    The DTS package runs fine if I run it manually on Enterprise Manager or if I use DTSRun in a command prompt.
    However when I try to run the package in a stored procedure or in query analyzer as follows:
    ie Exec master..xp_cmdshell 'DTSRUN /SSQL SERVER /NPackageName /Uuserid /Ppassword'

    The package does not return an error but it does not actually import any data from the Excel spreadsheet. It is able to truncate the table without any problem.
    I've tried all kinds of combination of using Windows user id that I know has access to the excel file instead of a sql login.
    I've run Filemon on the excel file and it seems like DTS is not accessing the file when I use the stored proc or query analyzer method.

    If anyone could shed any light on this I would be most grateful. Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by jekng
    The DTS package runs fine if I run it manually on Enterprise Manager or if I use DTSRun in a command prompt.
    However when I try to run the package in a stored procedure or in query analyzer as follows:
    ie Exec master..xp_cmdshell 'DTSRUN /SSQL SERVER /NPackageName /Uuserid /Ppassword'
    When you run the package manually, are you running it from your machine (do you have EM running on your machine and connected to the SQL Server)or are you logged on to the server directly?

    DTS looks for the file in the path of the machine it is running on ... so if the spreadsheet exists on your machine but not the server it will execute with success when run from your machine, but not by the SQL Agent on the server.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    May 2007
    Posts
    49
    Setup DTS logging and find out what is exaclty going on..
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  4. #4
    Join Date
    Jun 2007
    Posts
    2
    Quote Originally Posted by tomh53
    When you run the package manually, are you running it from your machine (do you have EM running on your machine and connected to the SQL Server)or are you logged on to the server directly?

    DTS looks for the file in the path of the machine it is running on ... so if the spreadsheet exists on your machine but not the server it will execute with success when run from your machine, but not by the SQL Agent on the server.
    Hi. I have successfully run the package on Enterprise Manager on my machine and using DTSRun on a command prompt on my machine. The DTS package is pointing to the excel file with a full UNC pathname.

    I've enabled DTS package logging and it does not return an error. The error code for the DataPump task is 0 even though it did not import any rows.

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Next place to check ... does the SQL Agent service login have permissions to the file share where the spreadsheet resides?

    -- This is all just a Figment of my Imagination --

Posting Permissions

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