Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Posts
    1

    Question Unanswered: How to call a DTS package from a Stored Proc?

    Does anyone have any code samples of a Stored Procedure executing a DTS package?

    I need to create an asp page that creates an XLS file based on a query when a button is pressed.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    You have 2 options:

    1. using sql server agent to execute the package and calling sp_start_job in your stored procedure.

    2. You can use xp_cmdshell with dtsrun.exe in your stored procedure -- however, xp_cmdshell might be locked down due to the power of this xp.

  3. #3
    Join Date
    Jan 2003
    Posts
    1
    Can you pls discuss futher of how to use the sql server agent to execute the package and calling sp_start_job in your stored procedure.

    Is there any sample code

  4. #4
    Join Date
    Jan 2003
    Posts
    1

    Wink

    CREATE PROCEDURE yourprocname AS exec master..xp_cmdshell 'DTSRun /~Z0x7B631A505E87BC91E5CC725BB8C229F694399C648906AE 718A6D093A0A79587FB3233BD54F20359BE3D5506B0ED3CBA6 1BC036BD1B464A76234946ED78F36AC6B37B8E6784D1DADA6E D34F62FF39C0251840764137FB9D3B4465ECFFE6A6761991C1 8093225FEB774D1477E8E1BE81676A602903827518CB1A74D2 C5F8C685D97879CE'
    GO

    a tip to have the long id of the dts task is to schedule it first, after open the scheduled process and open it's first step

  5. #5
    Join Date
    Jan 2003
    Posts
    8
    Keep in mind that if your going to let your web page run xp_cmdShell you are going to have to give your web account a lot of access to the SQL Server. This brings some major security problems into the mix. You have the same problem if you start a job.

    I'd recommend getting the result set from the SQL Server, and having the ASP page on the web server create a csv file on the web server. I usually use TarsusIO. You used to be able to get it from www.tarsus.com, but it looks like he didn't re-register his domain.

    Search google for "tarsusio", and you should be able to find somewhere to download it.

Posting Permissions

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