dts.pas

delphi使用sqlserver2000中dts导入导出数据

unit DTS;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DTS_TLB, OleServer,ComObj;

type
    TColumnList = packed record
    SourceColumns_Name : String;
    SourceColumns_Ordinal : Integer;
    SourceColumns_Flags : Integer;
    SourceColumns_Size : Integer;
    SourceColumns_DataType : Integer;
    SourceColumns_Precision : Integer;
    SourceColumns_NumericScale : Integer;
    SourceColumns_Nullable : Boolean;
    DestinationColumns_Name : String;
    DestinationColumns_Ordinal : Integer;
    DestinationColumns_Flags : Integer;
    DestinationColumns_Size : Integer;
    DestinationColumns_DataType : Integer;
    DestinationColumns_Precision : Integer;
    DestinationColumns_NumericScale : Integer;
    DestinationColumns_Nullable : Boolean;
    End;

    TgoPackage = packed record
    Name : String;
    WriteCompletionStatusToNTEventLog : Boolean;
    FailOnError : Boolean;
    PackagePriorityClass  : Integer;
    MaxConcurrentSteps : Integer;
    LineageOptions : Integer;
    UseTransaction : Boolean;
    TransactionIsolationLevel : Integer;
    AutoCommitTransaction : Boolean;
    RepositoryMetadataOptions : Integer;
    UseOLEDBServiceComponents : Boolean;
    LogToSQLServer : Boolean;
    LogServerFlags : Integer;
    FailPackageOnLogFailure : Boolean;
    ExplicitGlobalVariables : Boolean;
    PackageType : Integer;
    End;

    TDTSFlatFile = packed record
    oConnection_ConnectionProperties_DataSource : String;
    oConnection_ConnectionProperties_Mode : Integer;
    oConnection_ConnectionProperties_RowDelimiter : String;
    oConnection_ConnectionProperties_FileFormat : Integer;
    oConnection_ConnectionProperties_ColumnLengths : String;
    oConnection_ConnectionProperties_ColumnDelimiter : String;
    oConnection_ConnectionProperties_FileType : Integer;
    oConnection_ConnectionProperties_SkipRows : Integer;
    oConnection_ConnectionProperties_FirstRowColumnName : Boolean;
    oConnection_ConnectionProperties_ColumnNames : String;
    oConnection_ConnectionProperties_NumberofColumn : Integer;
    oConnection_ConnectionProperties_TextQualifierColMask : String;
    oConnection_ConnectionProperties_Maxcharactersperdelimitedcolumn : Integer;
    oConnection_ConnectionProperties_BlobColMask : String;
    oConnection_Name : String;
    oConnection_ID : Integer;
    oConnection_Reusable : Boolean;
    oConnection_ConnectImmediate : Boolean;
    oConnection_DataSource : String;
    oConnection_ConnectionTimeout : Integer ;
    oConnection_UseTrustedConnection : Boolean;
    oConnection_UseDSL : Boolean;
    IsUsed : Boolean;  //是否已经附值
    End;

    TDTSJetOLEDB = packed record
    oConnection_ConnectionProperties_UserID : String;
    oConnection_ConnectionProperties_DataSource : String ;
    oConnection_ConnectionProperties_ExtendedProperties : String;
    oConnection_Name : String;
    oConnection_ID : Integer;
    oConnection_Reusable : Boolean;
    oConnection_ConnectImmediate : Boolean;
    oConnection_DataSource : String;
    oConnection_UserID : String;
    oConnection_ConnectionTimeout : Integer;
    oConnection_UseTrustedConnection : Boolean;
    oConnection_UseDSL : Boolean;
    End;

    TDTSSQLOLEDB = packed record
    oConnection_ConnectionProperties_IntegratedSecurity : String;
    oConnection_ConnectionProperties_PersistSecurityInfo : Boolean;
    oConnection_ConnectionProperties_InitialCatalog : String;
    oConnection_ConnectionProperties_DataSource : String;
    oConnection_ConnectionProperties_ApplicationName : String;
    oConnection_Name : String;
    oConnection_ID : Integer;
    oConnection_Reusable : Boolean;
    oConnection_ConnectImmediate : Boolean;
    oConnection_DataSource : String;
    oConnection_ConnectionTimeout : Integer;
    oConnection_Catalog : String;
    oConnection_UseTrustedConnection : String;
    oConnection_UseDSL : Boolean;
    End;

    ToCustomTask = packed record
    oCustomTask1_Name : String;
    oCustomTask1_Description : String;
    oCustomTask1_SourceConnectionID : Integer;
    oCustomTask1_SourceObjectName : String ;
    oCustomTask1_SourceSQLStatement : String;
    oCustomTask1_DestinationConnectionID : Integer;
    oCustomTask1_DestinationObjectName : String;
    oCustomTask1_ProgressRowCount : Integer;
    oCustomTask1_MaximumErrorCount : Integer;
    oCustomTask1_FetchBufferSize : Integer;
    oCustomTask1_UseFastLoad : Boolean;
    oCustomTask1_InsertCommitSize : Integer;
    oCustomTask1_ExceptionFileColumnDelimiter : String;
    oCustomTask1_ExceptionFileRowDelimiter : String;
    oCustomTask1_AllowIdentityInserts : Boolean;
    oCustomTask1_FirstRow : String;
    oCustomTask1_LastRow : String;
    oCustomTask1_FastLoadOptions : Integer;
    oCustomTask1_ExceptionFileOptions : Integer;
    oCustomTask1_DataPumpOptions : Integer;
    End;

type TDTSUnit = Class
public
     constructor Create;
     function  Execute(Logfile,ModelFile:string;var Msg:String):Boolean;
     procedure tracePackageError(const oPackage : Package2 ;var Msg : String);
     procedure Task_Sub(goPackage:Package2 );
     procedure oCustomTask1_Trans_Sub(var oCustomTask1:DataPumpTask2;Index : Integer);
     Function  AddColumnList(S_Name:String;S_Flags,S_Size,S_DataType,S_Precision,S_NumericScale:Integer; S_Nullable : Boolean;
               D_Name : String; D_Flags, D_Size, D_DataType, D_Precision, D_NumericScale:Integer; D_Nullable : Boolean) : Boolean;
     Function  GetColumnList(ModelFile : String) : Boolean;
     Function  CheckText(LineText : String) : Boolean;
     function  Split(const Source,ch:string):TStringList;
     function  Instr(str: string;substr:string):integer;
     procedure InitializeFromText(DataFile : String) ;
     procedure InitializeFromExcel(DataFile : String; SheetName : String) ;
     procedure InitializeFromAccess(DataFile : String; TableName : String) ;
     procedure InitializeFromSQL(SQL_IP : String; SQL_DBname : String; SQL_Username : String; SQL_Password : String; SQL_TableName : String) ;
     procedure InitializeFromMySQL(MySQL_ODBCName : String; MySQL_TableName : String) ;
     procedure InitializeToText(DataFile : String) ;
     procedure InitializeToExcel(DataFile : String; SheetName : String) ;
     procedure InitializeToAccess(DataFile : String; TableName : String) ;
     procedure InitializeToSQL(SQL_IP : String; SQL_DBname : String; SQL_Username : String; SQL_Password : String; SQL_TableName : String) ;
     procedure InitializeToMySQL(MySQL_ODBCName : String; MySQL_TableName : String) ;
end;

implementation

var goPackageOld:Package ;
var goPackage:Package2;
var DataFile1, DataFile2, TableName1, TableName2, ErrOutPutFile, SQLserverIP1, Username1, DBname1, Password1, SQLserverIP2, Username2, DBname2, Password2 : String;
var ColumnList : array[1..100] of TColumnList;  //字段对应表
var ColumnListCount : Integer;
var InitializeFromMode, InitializeToMode : Integer;
var goPackageValues : TgoPackage ;
var DTSFlatFile : array[1..3] of TDTSFlatFile;
var oCustomTaskValue : ToCustomTask;
var S, D : Boolean;   //读取文本时标志字段位置

constructor TDTSUnit.Create;
begin
    inherited create;
end;

procedure TDTSUnit.InitializeFromText(DataFile : String) ;
begin
     DataFile1 := DataFile;
     TableName1 := DataFile;
     InitializeFromMode := 1;
End;

procedure TDTSUnit.InitializeFromExcel(DataFile : String; SheetName : String) ;
begin
     DataFile1 := DataFile;
     TableName1 := SheetName;
     InitializeFromMode := 2;
End ;

procedure TDTSUnit.InitializeFromAccess(DataFile : String; TableName : String) ;
begin
     DataFile1 := DataFile ;
     TableName1 := TableName ;
     InitializeFromMode := 3 ;
End ;

procedure TDTSUnit.InitializeFromSQL(SQL_IP : String; SQL_DBname : String; SQL_Username : String; SQL_Password : String; SQL_TableName : String);
begin
     TableName1 := SQL_TableName;
     SQLserverIP1 := SQL_IP;
     Username1 := SQL_Username;
     DBname1 := SQL_DBname;
     Password1 := SQL_Password;
     InitializeFromMode := 4;
End ;

procedure TDTSUnit.InitializeFromMySQL(MySQL_ODBCName : String; MySQL_TableName : String);
begin
     TableName1 := MySQL_TableName ;
     DBname1 := MySQL_ODBCName ;
     InitializeToMode := 5 ;
End ;

procedure TDTSUnit.InitializeToText(DataFile : String);
begin
     DataFile2 := DataFile ;
     InitializeToMode := 1 ;
End ;

procedure TDTSUnit.InitializeToExcel(DataFile : String; SheetName : String);
begin
     DataFile2 := DataFile;
     TableName2 := SheetName;
     InitializeToMode := 2;
End ;

procedure TDTSUnit.InitializeToAccess(DataFile : String; TableName : String);
begin
     DataFile2 := DataFile;
     TableName2 := TableName;
     InitializeToMode := 3;
End ;

procedure TDTSUnit.InitializeToSQL(SQL_IP : String; SQL_DBname : String; SQL_Username : String; SQL_Password : String; SQL_TableName : String);
begin
     TableName2 := SQL_TableName;
     SQLserverIP2 := SQL_IP;
     Username2 := SQL_Username;
     DBname2 := SQL_DBname;
     Password2 := SQL_Password;
     InitializeToMode := 4;
End ;

procedure TDTSUnit.InitializeToMySQL(MySQL_ODBCName : String; MySQL_TableName : String);
begin
     TableName2 := MySQL_TableName;
     DBname2 := MySQL_ODBCName;
     InitializeToMode := 5;
End ;

Function TDTSUnit.Execute(Logfile,ModelFile:string;var Msg:String):Boolean;
var oConnection :Connection;
    Desindex : Integer;
    oStep : Step2;
    //oPrecConstraint : PrecedenceConstraint;
begin
    ErrOutPutFile := Logfile;
    
    If (InitializeFromMode = 0) Then
    begin
        Msg := '源数据没有初始化';
        Execute := False;
        Exit;
    End;
    
    If (InitializeToMode = 0) Then
    begin
        Msg := '目标数据没有初始化';
        Execute := False;
        Exit;