Passing parameter to DTS | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Passing parameter to DTS

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 ?

http://www.windowsitpro.com/SQLServer/Article/ArticleID/7836/7836.html andhttp://www.sqldts.com is a good resource for such features. 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.
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.
]]>