execute DTS packages

Question

execute DTS packages

oren koler

Right there with Babe

Points: 776
More actions
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 )

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

oren koler Right there with Babe Points: 776 More actions · Answer 2

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 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 4

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

oren koler Right there with Babe Points: 776 More actions · Answer 5

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 ??

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

Andy Hilliard Old Hand Points: 344 More actions · Answer 7

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

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

Andy Hilliard
Owl Creek Consulting[/url]

oren koler Right there with Babe Points: 776 More actions · Answer 8

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

corey lawson Hall of Fame Points: 3773 More actions · Answer 9

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.