-
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 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 ?
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|