SQL Server Performance

Passing parameter to DTS

Discussion in 'SQL Server DTS-Related Questions' started by yurich, Sep 9, 2005.

  1. yurich New Member

    I have DTS package that copies data from view to data file.
    I run this DTS package from my stored procedure using dtsrun:
    ================================================== =====
    CREATE PROCEDURE spRunDTS
    @DTS_Name varchar(50) = 'DTS_GetInstrumentsData',
    @DTSRUN_Path varchar(200) = '"C:pROGRAM FILESMICROSOFT SQL Server80TOOLSBINNDtsrun.exe"',
    @ServerName varchar(20) = 'MyServer',
    @UserID varchar(20) = 'sa',
    @Password varchar(20) = '123'

    AS

    DECLARE @cmd varchar(300)

    SET @cmd = @DTSRUN_Path + ' /S ' + @ServerName + ' /U ' + @UserID + ' /P ' + @Password + ' /N ' + @DTS_Name

    EXEC master.dbo.xp_cmdshell @cmd
    ================================================== =====

    It works fine, but now I have to work with different data files and I need to pass name of destination data file to DTS. How to implement that ?
  2. satya Moderator

  3. danielforrester New Member

    I've not done this but you should be able to do this using global variables within the DTS package which can be passed on the command line of DTSRun.

Share This Page