DTS Parameter Passing example

DTS Parameter Passing example

--1. Set up a test table

CREATE TABLE TEMPDB.DBO.TB (id int)

--2. Create a new DTS package

To create in a package:
A. Microsoft OLE DB Provider for SQL Server connection
B. Executing SQL tasks
The task's SQL statement is written on: Insert TB values (?)
Click the Parameters button to open the Parameter Mapping dialog box
Click the Create global variable button to create a global variable named value with the type integer and the value 1
Click OK to go back to the Parameter Mapping dialog box, and in the Parameter Mappings list, select value for parameter 1, enter global variable
Click the OK button to go back to the DTS Package Definition window
C. Save DTS Package with package name specified as a, location specified as SQL Server

--3. Creates a new package to invoke package a

To create in a package:
A. Perform package tasks
In the package name of the General option, select the package name created in step 2
In the external global Variables option, enter the variable name value. (The global variable for the package in step 2)
B. Save the DTS package with the package name specified as B and the location specified as SQL Server

--4. Execute Package, view results

EXEC Master.dbo.xp_cmdshell ' dtsrun/s/e/n "B"/A "value": 3 = "2" '
EXEC Master.dbo.xp_cmdshell ' dtsrun/s/e/n "B"/A "value": 3 = "3" '
EXEC Master.dbo.xp_cmdshell ' dtsrun/s/e/n "B"/A "value": 3 = "5" '
EXEC Master.dbo.xp_cmdshell ' dtsrun/s/e/n "B"/A "value": 3 = "10" '
SELECT * from TEMPDB.DBO.TB
DROP TABLE TEMPDB.DBO.TB

CSDN Community Post Address