先祝大家新春快乐，这里还有个小问题，请高手帮助。delphi+DTS

Question

使用DTS向导导入数据成功。但另存为.bas文件后转成delphi后程序无法实现数据导入的功能。代码有在网上找的，也有.bas转过来的。不好意思，过年还麻烦大家。祝各位大侠猪年好运！

Answer 1

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, OleServer, DTS_TLB, StdCtrls, Buttons, DTSPump_TLB;
....
//这段是网上抄的，没反应
procedure TForm1.BitBtn1Click(Sender: TObject);
procedure oCustomTask2_Trans_S1(oCustomTask2: DataPumpTask2);
var
   oTransformationOld: Transformation;
   oTransformation: Transformation2;
begin
   oTransformationOld := oCustomTask2.Transformations.New('DTS.DataPumpTransformCopy');
   oTransformation := oTransformationOld as Transformation2;
   oTransformation.Name := 'DirectCopyXform';
   oTransformation.TransformFlags := 63;
   oTransformation.ForceSourceBlobsBuffered := 0;
   oTransformation.ForceBlobsInMemory := False;
   oTransformation.InMemoryBlobSize := 1048576;
   oTransformation.TransformPhases := 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.Properties:='Excel 8.0;HDR=YES;';
   dts_conn.ID:=1;
   dts_conn.DataSource:='D:\小区管理软件\申报.xls';   dts_conn2:=opackage.Connections.New('SQLOLEDB');
   dts_conn2.ID:=2;
   dts_conn2.DataSource:='.';
   dts_conn2.UserID:='sa';
   dts_conn2.Password:='';
   dts_conn2.Catalog:='经济区管理库';   opackage.Connections.Add(dts_conn);
   opackage.Connections.Add(dts_conn2);
   dts_step:=opackage.Steps.New;
   dts_task:=opackage.Tasks.New('DTSDataPumpTask');
   dts_task.Name:='导入Etax数据到经济区管理库';
   dts_customtask:=dts_task.CustomTask;
   dts_pumptask:=dts_customtask as datapumptask2;
   dts_pumptask.Name:='导入Etax数据';   dts_pumptask.SourceConnectionID:=1;
   dts_pumptask.SourceSQLStatement:='select id1,id2,name,classid,classstr,tradeid,taxamout from 申报$ where id1<>'' order by id1';
   //dts_pumptask.SourceObjectName:='table1'; //(取消注释也没用)
   dts_pumptask.DestinationConnectionID:=2;
   //s_pumptask.DestinationSQLStatement:='select * from table1';
   dts_pumptask.DestinationObjectName:='etaxdatain';   dts_pumptask.ProgressRowCount:=100;
   dts_pumptask.ProgressRowCount := 1000;
   dts_pumptask.MaximumErrorCount := 0;
   dts_pumptask.FetchBufferSize := 1;
   dts_pumptask.UseFastLoad := True;
   dts_pumptask.InsertCommitSize := 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;
   dts_step.ExecuteInMainThread:=true;
   oCustomTask2_Trans_S1(dts_pumptask);
   opackage.Tasks.Add(dts_task);
   opackage.Steps.Add(dts_step);
   opackage.Execute;
   opackage.UnInitialize;
end;

Answer 2

//这段是我自己用sql生成的bas文件转过来的，有一段编译不过，可以创建表，但就是不能导入数据。请做的高手帮助，修正一下。再次感谢各位。
procedure TForm1.BitBtn2Click(Sender: TObject);
procedure Task_Sub1(goPackage: package2);
  var
    oTask:Task ;
    oLookup:Lookup ;
    dts_customtask:CustomTask;
    oCustomTask1:ExecuteSQLTask2;
begin
    oTask:=goPackage.Tasks.New('DTSExecuteSQLTask');
    dts_customtask:= oTask.CustomTask;
    oCustomTask1:=dts_customtask as ExecuteSQLTask2;
    oCustomTask1.Name:= '创建表 EtaxDataIn 任务';
    oCustomTask1.Description:= '创建表 EtaxDataIn 任务';
    oCustomTask1.SQLStatement:= 'CREATE TABLE [EtaxDataIn] ('+#13#10+
                                '[id1] nvarchar (30) NULL,'+#13#10+
                                '[id2] nvarchar (30) NULL, '+#13#10+
                                '[name] nvarchar (80) NULL, '+#13#10+
                                '[classid] nvarchar (5) NULL, '+#13#10+
                                '[classstr] nvarchar (5) NULL, '+#13#10+
                                '[tradeid] nvarchar (20) NULL, '+#13#10+
                                '[taxamout] float NULL)';
    oCustomTask1.ConnectionID:= 2 ;
    oCustomTask1.CommandTimeout:= 0 ;
    oCustomTask1.OutputAsRecordset:= False;
    goPackage.Tasks.Add(oTask);
    oCustomTask1:=nil;
    oTask:=nil;
End ;

Answer 3

接上...
procedure Task_Sub2(goPackage: package2);
procedure oCustomTask2_Trans_Sub1(oCustomTask2:DataPumpTask2);
   var
        oTransformationOld: Transformation;
        oTransformation: Transformation2;
        oTransProps:Properties;
        oColumn:Column ;
begin
   oTransformationOld := oCustomTask2.Transformations.New('DTS.DataPumpTransformCopy');
   oTransformation := oTransformationOld as Transformation2;
   oTransformation.Name := 'DirectCopyXform';
   oTransformation.TransformFlags := 63;
   oTransformation.ForceSourceBlobsBuffered := 0;
   oTransformation.ForceBlobsInMemory := False;
   oTransformation.InMemoryBlobSize := 1048576;
   oTransformation.TransformPhases := 4;
   oColumn:= oTransformation.SourceColumns.New('id1', 1);
                        oColumn.Name:='id1';
                        oColumn.Ordinal:=1;
                        oColumn.Flags:=102;
                        oColumn.Size:= 30;
                        oColumn.DataType:= 130 ;
                        oColumn.Precision:= 0 ;
                        oColumn.NumericScale:= 0;
                        oColumn.Nullable:= True ;
                oTransformation.SourceColumns.Add(oColumn);
                oColumn:=nil;
   oColumn:= oTransformation.SourceColumns.New('id2', 2);
                        oColumn.Name := 'id2';
                        oColumn.Ordinal:= 2 ;
                        oColumn.Flags:= 102 ;
                        oColumn.Size:= 30;
                        oColumn.DataType:= 130 ;
                        oColumn.Precision:= 0 ;
                        oColumn.NumericScale:= 0;
                        oColumn.Nullable:= True ;
                oTransformation.SourceColumns.Add(oColumn);
                oColumn:=nil;
    oColumn:= oTransformation.SourceColumns.New('name', 3);
                        oColumn.Name := 'id2';
                        oColumn.Ordinal:= 3 ;
                        oColumn.Flags:= 102 ;
                        oColumn.Size:= 80;
                        oColumn.DataType:= 130 ;
                        oColumn.Precision:= 0 ;
                        oColumn.NumericScale:= 0;
                        oColumn.Nullable:= True ;
                oTransformation.SourceColumns.Add(oColumn);
                oColumn:=nil;
    oColumn:= oTransformation.SourceColumns.New('classid', 4);
                        oColumn.Name := 'classid';
                        oColumn.Ordinal:= 4 ;
                        oColumn.Flags:= 102 ;
                        oColumn.Size:= 8;
                        oColumn.DataType:= 130 ;
                        oColumn.Precision:= 0 ;
                        oColumn.NumericScale:= 0;
                        oColumn.Nullable:= True ;
                oTransformation.SourceColumns.Add(oColumn);
                oColumn:=nil;
    oColumn:= oTransformation.SourceColumns.New('classstr', 5);
                        oColumn.Name := 'classstr';
                        oColumn.Ordinal:=5 ;
                        oColumn.Flags:= 102 ;
                        oColumn.Size:= 8;
                        oColumn.DataType:= 130 ;
                        oColumn.Precision:= 0 ;
                        oColumn.NumericScale:= 0;
                        oColumn.Nullable:= True ;
                oTransformation.SourceColumns.Add(oColumn);
                oColumn:=nil;
    oColumn:= oTransformation.SourceColumns.New('tradeid', 6);
                        oColumn.Name := 'tradeid';
                        oColumn.Ordinal:= 6 ;
                        oColumn.Flags:= 102 ;
                        oColumn.Size:= 8;
                        oColumn.DataType:= 130 ;
                        oColumn.Precision:= 0 ;
                        oColumn.NumericScale:= 0;
                        oColumn.Nullable:= True ;
                oTransformation.SourceColumns.Add(oColumn);
                oColumn:=nil;
    oColumn:= oTransformation.SourceColumns.New('taxamout', 7);
                        oColumn.Name:= 'taxamout';
                        oColumn.Ordinal:= 7 ;
                        oColumn.Flags:= 118;
                        oColumn.Size:= 0 ;
                        oColumn.DataType:= 5;
                        oColumn.Precision:= 0 ;
                        oColumn.NumericScale:= 0;
                        oColumn.Nullable:= True;
                oTransformation.SourceColumns.Add(oColumn);
                oColumn:=nil;

Answer 4

天哪，晕死了。
用SQL SERVER转出SQL脚本，然后随便用什么环境都可以执行SQL脚本创建库、表，最后是数据导入。

Answer 5

建一个DTS包,然后执行uses ..COMObj;var
  ObjPackage, ObjStep, ObjTask, ObjExecPkg: variant;
begin
  ObjPackage := CreateOLEObject('DTS.Package2');
  ObjPackage.failonerror := true;
  ObjStep    := ObjPackage.steps.new;
  ObjTask    := ObjPackage.tasks.new('DTSExecutePackageTask');
  ObjExecPkg := ObjTask.customtask;  ObjExecPkg.PackagePassword := 'user';
  ObjExecPkg.FileName := 'C:\DTS_UE\TestPkg\VarPubsFields.dts';
  ObjExecPkg.Name := 'ExecPkgTask';  ObjStep.TaskName := objExecPkg.Name;
  ObjStep.Name := 'ExecPkgStep';
  ObjStep.ExecuteInMainThread := True;  ObjPackage.Steps.Add(ObjStep);
  ObjPackage.Tasks.Add(ObjTask);
  ObjPackage.Execute;  ObjExecPkg := Null;
  ObjTask := Null;
  ObjStep := Null;  ObjPackage.UnInitialize;
end; 

Answer 6

问题解决了，可以在日志中查询执行的结果。DTSPackage2.LogFileName:= 'C:\log.txt';