SQL Server Performance Forum – Threads Archive
dtsrun execution by endusers
Hi, I have a DTS package that should be executed by end-users (i.e. users where SQLserver is not installed).I have a .cmd command file that executes dtsrun with all parameters needed. Is there a way to allow end-users to execute this package without installing SQL server software that they should not have (e.g. an ‘end-user installation’ of SQL server?) thanks, Joop
Run to run DTSRUN utility you require PACKAGE_GUID_STRING which is the package ID assigned to the DTS package when it was created. The package ID is a GUID. You can try copying DTSRUN.* files to a client machine where SQL tools aren’t installed and specify the required GUID and rest of parameters to execute it from command prompt. Refer to books online for more information on executing DTSRUN utility from command prompt. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
All your clients need is the ability to connect to the SQL Server. consider using dtsrunui: If you are having problems with the syntax for the dtsrun command line, try using the dtsrunui utility to generate the commandline string. It is very similar to dtsrun, but with a user interface. This allows you to easily select your package, including settings for global variables and logging, then use the generate function to create a valid dtsrun command line. There is no shortcut provided for dtsrunui, so just type dtsrunui in the Windows Run dialog or from a command prompt. Nathan H.O.
Moderator
SQL-Server-Performance.com
Copying dtsrun.* does not work. DTSRUNUI is not the tool that I want end-users to use. We solved the problem by defining an SP that executes a commandfile that has the DTSRUN command (with all parameters needed).<br /><br />The SP looks like this:<br /><br />DECLARE @StartDTS NVARCHAR(100)<br /><br />Set @StartDTS = ‘"<<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />ath to .cmd file>"'<br /><br /> EXEC master..xp_cmdshell @StartDTS <br /><br />This SP can be executed in many ways by end-users.<br /><br />Joop<br /><br /> <br />
I did the same for my developers team. The end user needed to run a DTS package and I created the following stored procedure: CREATE PROCEDURE sp_ImportData
@rtn int OUTPUT
AS exec @rtn=master.dbo.xp_cmdshell ‘dtsrun /S 100.90.80.70 /E /N dt_ImportTextFiles’ GO Which 100.90.80.70 is my server’s IP. Developers then, called the stored procedure from within their application. Here is a sample call from within Query Analyser: DECLARE @RetVal Int EXEC sp_ImportData @RetVal
]]>