Post reply

execute DTS packages

  • January 4, 2006 at 3:36 am

    #90375

    hi all

    how can i execute a local package i made in DTS

    using sql

    ( i want to execute the package in a trigger )

  • January 4, 2006 at 3:51 am

    #612547

    why would you want to call a DTS package in a trigger? Trigger code should be as short as possible and do as little as possible.

    What are you trying to do?

    That said...

    exec master..xp_cmdshell 'DTSRUN <package name and stuff goes here>'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 4, 2006 at 10:33 pm

    #612772

    because i am working with an old program that reads only txt files

    so if i want the old program to be up to date - ive got to use a trigger .

    a scheduled task - just wont do it.

    any how - data is inserted very rarely and the dts job is very short.

    thanks for the tip 🙂

  • January 4, 2006 at 10:59 pm

    #612775

    Just bear a few security issues in mind. A fairly high priviledged account is required to use xp_cmdshell. Check Books online for the exact details

    Commands run from xp_cmdshell run as the start-up account for SQL server  (unless you do some complex setting up of proxies) and, since SQL usually runs as a high-priviledge user, anything run from cmdshell has a lot of permissions on the server.

     

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 5, 2006 at 12:46 am

    #612787

    Have practically same problem.

    I have a dts package running just fine.

    When I schedule it (right click in local packages) every time it runs i get an error. It doesn't find the file.

    Kind regards

    El Jefe


    JV

  • January 5, 2006 at 1:07 am

    #612790

    ure right about the privilegesin using the xp_cmdshell

    im not a sysadmin - and i well never be

    so what kind of role do i have to get to use xp_cmdshell ??

     

     

  • January 5, 2006 at 1:38 am

    #612800

    JV: check the account that SQL Agent runs as. Make sure it has permission to access the file. Make sure you use full paths, not relative paths

    Regarding permissions for xp_cmdshell, straight from Books Online

    When you grant execute permissions [on xp_cmdshell] to users, the users can execute any operating-system command at the Microsoft Windows NT® command shell that the account running Microsoft SQL Server™ has the needed privileges to execute.

    By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure.

    When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.

    Note  In earlier versions, a user who was granted execute permissions for xp_cmdshell ran the command in the context of the MSSQLServer service's user account. SQL Server could be configured (through a configuration option) so that users who did not have sa access to SQL Server could run xp_cmdshell in the context of the SQLExecutiveCmdExec Windows NT account. In SQL Server 7.0, the account is called SQLAgentCmdExec. Users who are not members of the sysadmin fixed server role now run commands in the context of this account without specifying a configuration change.

    Permissions

    Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.

    Important  If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 5, 2006 at 1:46 am

    #612803

    This was also cross-posted in the DTS forums. I believe the thread there answers the question pretty completely.

     

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=247818

    Andy Hilliard
    Owl Creek Consulting[/url]

  • January 5, 2006 at 2:29 am

    #612811

    as i understand it - in sql2000 onwindows - only sysadmin can use xp_cmdshell

     

     

  • January 5, 2006 at 2:42 pm

    #613047

    For SS2000, there are a couple of TSQL scripts to run (I think I found them on SQLDTS.com, or possibly msdn) that set things up to call COM/OLE objects from TSQL (of which DTS packages are).

    Then, I'd write a proc to call the package, and then call the proc from the trigger.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply