Delphi的DTS编程

一、DTS简介

DTS为导入导出模块，在SQL SERVER中有专门的导入导出工具

 

二、调用SQL SERVER导出的包

若要执行保存为COM结构化的存储文件DTS包，使用dtsrun /Ffilename /Npackage_name /Mpackage_password

若要执行保存在SQL SERVER msdb中的DTS包，使用

dtsrun /Sserver_name /Uuser_name　/Ppassword /Npackage_name /Mpackage_password

若要执行保存在Meta Data Services中的DTS包，使用:

dtsrun /Sserver_name /Userver_name /Ppassword /Npackage_name /Mpackage_password /Rrespository_name

 

例如：服务器名为server,用户名：sa,密码：test，包名：SaleToAccess，包密码：test

var

    str:string;

begin

    str :='exec master.dbo.xp_cmdshell "dtsrun /Sserver /Usa /Ptest /NSaleToAccess /Mtest"';

    adoquery1.Close;

    adoquery1.Sql.Text :=str;

    adoquery1.Open;

end;

 

三、利用程序直接调用DTS

1.首先在Delphi中加入Microsoft DTSPackage Object Library

2.在工程中引用DTS_TLB

3.在窗体中加入一按钮，假定在C盘下有1.mdb,2.mdb,且两库的表名与结构都相同，1.mdb中有数据，2.mdb中无数据，现要求将1.mdb的cailiao_gongying表中的数据导入到2.mdb的cailiao_gongying中去，代码实现如下：

procedure TForm1.Button1Click(Sender:TObject) ;

 

    procedure oCustomTask2_Trans_S!(oCustromTask2:DataPumpTask2);

    var

        oTransformationOld:Transformation2;

        oTransformation:Transformation2;

    begin

        oTransformationOld :=oCustomTask2.TransformationOld as Transformation2;

        oTransformation :='DirectCopyXform'; 

        oTransformation.TransformFlags :=63;

        oTransformation.ForceSourceBlobsBuffered :=0;

        oTransformation.InMemoryBlobSize :=1048576;

        oTransformation.TransformPhaces :=4;

        oCustomTask2.Transformations.Add(oTransformation);

        oTransformation :=nil;

        oTransformationOld :=nil;

    end;

 

var

    opackageold:package;

    opackage:package2;

    dts_conn,dts_conn2:connection;

    dts_task:task;

    dts_customtask:customtask;

    dts_pumptask:datapumptask2;

    dts_step:step;

begin

    opackageold:=CoPackage.Create;

    opackage:=opackageold as package2;

    dts_conn:=opackage.Connections.new('Microsoft.Jet.OLEDB.4.0');

    dts_conn.ID :=1;

    dts_conn.DataSource:='C:/1.mdb';

    dts_conn2:=opackage.Connections.new('Microsoft.Jet.OLEDB.4.0');

    dts_conn2.ID :=2;

    dts_conn2.DataSource:='C:/2.mdb';

    opackage.Connections.Add(dts_conn);

    opackage.Connections.Add(dts_conn2);

    dts_step:=opackage.Step.New;

    dts_task:=opackage.Task.New('DTSDataPumpTask');

    dts_task.Name:='Copy Data from 1.mdb to 2.mdb';

    dts_customtask:=dts_task.CustomTask;

    dts_pumptask:=dts_customtask as dtapumptask2;

    dts_pumptask.Name:='Copy Data from 1.mdb to 2.mdb';

    dts_pumptask.SourceConnectionID :=1;

    dts_pumptask.SourceSQLStament:='select * from cailiao_gongying';

    dts_pumptask.DestictionConnectionID:=2;

    dts_pumptask.DestictionSQLStatement:='select * from cailiao_gongying';

    dts_pumptask.progressRowCount:=100;

    dts_pumptask.MaximumErrorCount:=0;

    dts_pumptask.FetchBufferSize :=1;

    dts_pumptask.UseFastLoad:=True;

    dts_pumptask.InsertComitSize:=0;

    dts_pumptask.ExceptionFileColumnDelimiter:='|';

    dts_pumptask.ExceptionFileRowDelimiter:=#13#10;

    dts_pumptask.AllowIdentityInserts:=False;

    dts_pumptask.FirstRow:=0;

    dts_pumptask.LastRow:=0;

    dts_pumptask.FastLoadOptions:=2;

    dts_pumptask.ExceptionFileOptions:=1;

    dts_pumptask.DataPumpOptions:=0;

    dts_step.Name:='LowerCaseStep';

    dts_step.TaskName:=dts_pumptask.Name;

    oCustomTask2_Trans_S1(dts_pumptask);

    opackage.Tasks.Add(dts_taskk);

    opackage.Steps.Add(dts_step);

    opackage.Execute;

    opackage.UnInitialize;

end;