This article mainly explains the practical steps for invoking SQL Server's DTS (Data Transformation Services) packages on the client side, as well as a description of the related testing environment. In actual operations, many netizens frequently ask how to invoke some scripts and objects on the SQL Server server side from the client side, and how to input parameters.
I will provide an example illustrating how to invoke SQL Server's DTS package on the client side, including parameter input and log recording:
---
**Testing Environment:**
**Server:** PIII 866 + 512MB + RAID5
WINDOWS 2000 SERVER + SP4; SQL Server 2000 + SP3
**Client:**
WINDOWS 2000 PRO + SP4 + SQL Server 2000 CLIENT + SP3
---
**Steps:**
1. **Create a DTS Package on the Server Side:**
This can be done step by step according to the wizard. For example, we created a DTS package that imports an Excel file into SQL Server: **DTS_ExcelToSQLServer**.
2. **Create a Batch File on the Client Side:**
Name it **CRUNDTS.BAT**.
3. **Edit with a Text Editor:**
```batch
@echo off
if [%1] == [] goto Usage
if [%2] == [] goto Usage
if [%3] == [] goto Usage
if [%4] == [] goto Usage
cls
SET LOGFILE=c:\DTSRUN.LOG
echo %TIME% Manual DTS package start run......... >> %LOGFILE%
echo ------------------------------------------ >> %LOGFILE%
@dtsrun /S %1 /U %2 /P %3 /N %4 >> %LOGFILE%
if errorlevel 1 goto Error
echo %TIME% Manual DTS package end......... >> %LOGFILE%
echo ------------------------------------------ >> %LOGFILE%
PAUSE
goto FINISH
:Error
echo ------------------------------------------- >> %LOGFILE%
echo DTSRUN ERROR PLS CHECK LOG .........
echo %TIME% GlobalStore Backup abnormal end. >> %LOGFILE%
pause
goto FINISH
:Usage
echo.
echo Usage: crundts %1 servername %2 use_name %3 password %4 dts_name
:FINISH
```
4. **Save the File:**
After saving this file, you can call it from any program or execute it manually by entering the required parameters.
5. **Test Run:**
Run the following command in the DOS prompt:
```
CRUNDTS SERVERNAME sa password DTS_ExcelToSQLServer
```
6. **Log Information:**
Example output:
```
10:24:29.38 Manual DTS package start run.........
DTSRun: Loading...
Error: -2147217900 (80040E14); Provider Error: 14262 (37B6)
Error string:
Error source:
Help file:
Help context: 0
10:24:29.66 Manual DTS package end.........
10:27:29.41 Manual DTS package start run.........
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 23 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 23
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
10:27:30.02 Manual DTS package end.........
```
The above content introduces how to invoke SQL Server's DTS package on the client side, and I hope you find it useful.
Article Address: 【Eden Network】http://www.edenw.com/tech/devdeloper/database/2010-07-14/4680.html