Passing parameter to DTS

[yurich]

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 FILES\MICROSOFT SQL Server\80\TOOLS\BINN\Dtsrun.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 ?

[skhanal]

You have to use global variable within the DTS and use that instead of hard coded data file name. Then you have to use SP_OA procedure to update the variable from your stored procedure then run dtsrun.