---所有配置做完后,现在就是调用它:笔者介绍三种调用方式:A:命令
开始:dtsrunui (有向导,按照向导,为每一个参数赋予值,就可以了)一般用于测试B:包调用
这个也有相关书籍介绍,笔者略C:存储过程调用CREATE PROCEDURE my_proc1 AS
EXEC master.dbo.xp_cmdshell 'dtsrun /S /E /N"水质项目监测数据导入" /A"ServerName":8= /A"FileName":8="D:\SZDATA\ExcelData\AppraiseData.xls" /A"DataSource":8="SZ"'
GO笔者稍微说明下参数定义:
/S 服务器 /E 信任连接 /N 包名
/ ServerName: 为空 ,8:全局参数类型为字符
/A 表示全局变量D:利用游标循环调用DTS--包参数调用的另一种方式:
DECLARE @STCD varchar(10)
DECLARE STCDS_CURSOR CURSOR FOR
OPEN STCDS_CURSORFETCH NEXT FROM STCDS_CURSOR
INTO @STCDWHILE @@FETCH_STATUS = 0
BEGIN
--调用包代码
EXEC('master.dbo.xp_cmdshell ''dtsrun /S /E /N"新建包" /A"STCD":3="'+@STCD+'"''')
FETCH NEXT FROM STCDS_CURSOR
INTO @STCD
ENDCLOSE STCDS_CURSOR
DEALLOCATE STCDS_CURSOR
GO
E:.net调用方式:
//说明需要添加 Microsoft.SqlServer.DTSPkg80.Package2Class /// <summary>
/// 运行DTS(Data Transformation Services )
/// </summary>
/// <returns></returns>
public string runDTS()
{
try
{
string returnValue; Microsoft.SqlServer.DTSPkg80.Package2Class package = new Microsoft.SqlServer.DTSPkg80.Package2Class();
string fileName = "C:\\DTStest.dts";
string password = null;
string packageID = null;
string versionID = null;
string name = "DTStest";
object pVerpersistStfOfHost = null;
package.LoadFromStorageFile(fileName,password,packageID,versionID,name,ref pVerpersistStfOfHost); package.Execute();
package.UnInitialize();
package=null;
returnValue = "success";
return returnValue; }
catch(Exception ex)
{
throw ex;
} }
--
作者5种方式均测试通过.
开始:dtsrunui (有向导,按照向导,为每一个参数赋予值,就可以了)一般用于测试B:包调用
这个也有相关书籍介绍,笔者略C:存储过程调用CREATE PROCEDURE my_proc1 AS
EXEC master.dbo.xp_cmdshell 'dtsrun /S /E /N"水质项目监测数据导入" /A"ServerName":8= /A"FileName":8="D:\SZDATA\ExcelData\AppraiseData.xls" /A"DataSource":8="SZ"'
GO笔者稍微说明下参数定义:
/S 服务器 /E 信任连接 /N 包名
/ ServerName: 为空 ,8:全局参数类型为字符
/A 表示全局变量D:利用游标循环调用DTS--包参数调用的另一种方式:
DECLARE @STCD varchar(10)
DECLARE STCDS_CURSOR CURSOR FOR
OPEN STCDS_CURSORFETCH NEXT FROM STCDS_CURSOR
INTO @STCDWHILE @@FETCH_STATUS = 0
BEGIN
--调用包代码
EXEC('master.dbo.xp_cmdshell ''dtsrun /S /E /N"新建包" /A"STCD":3="'+@STCD+'"''')
FETCH NEXT FROM STCDS_CURSOR
INTO @STCD
ENDCLOSE STCDS_CURSOR
DEALLOCATE STCDS_CURSOR
GO
E:.net调用方式:
//说明需要添加 Microsoft.SqlServer.DTSPkg80.Package2Class /// <summary>
/// 运行DTS(Data Transformation Services )
/// </summary>
/// <returns></returns>
public string runDTS()
{
try
{
string returnValue; Microsoft.SqlServer.DTSPkg80.Package2Class package = new Microsoft.SqlServer.DTSPkg80.Package2Class();
string fileName = "C:\\DTStest.dts";
string password = null;
string packageID = null;
string versionID = null;
string name = "DTStest";
object pVerpersistStfOfHost = null;
package.LoadFromStorageFile(fileName,password,packageID,versionID,name,ref pVerpersistStfOfHost); package.Execute();
package.UnInitialize();
package=null;
returnValue = "success";
return returnValue; }
catch(Exception ex)
{
throw ex;
} }
--
作者5种方式均测试通过.
我钢材用了,但是报错"不是hex"