dts.pas

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

                oConnection.UserID := Username2;
                oConnection.Password := Password2;
                oConnection.ConnectionTimeout := 60;
                oConnection.Catalog := DBname2;
                oConnection.UseTrustedConnection := False;
                oConnection.UseDSL := False;

                goPackage.Connections.Add(oConnection);
                oConnection := nil;
    end
    Else If InitializeToMode = 5 Then
    begin
                oConnection := goPackage.Connections.New('MSDASQL');
        
                oConnection.ConnectionProperties.Item('Persist Security Info').Value := True;
                oConnection.ConnectionProperties.Item('Data Source').Value := DBname2;
                
                oConnection.Name := 'MySQL ODBC 3.51 Driver';
                oConnection.ID := 2;
                oConnection.Reusable := True;
                oConnection.ConnectImmediate := False;
                oConnection.DataSource := DBname2;
                oConnection.ConnectionTimeout := 60;
                oConnection.UseTrustedConnection := False;
                oConnection.UseDSL := False;
                oConnection.UserID := Username2;
                oConnection.Password := Password2;
        
                goPackage.Connections.Add(oConnection);
                oConnection := nil;
    End;
    
    //---------------------------------------------------------------------------
    // create package steps information
    //---------------------------------------------------------------------------
    
    oStep := goPackage.Steps.New as Step2;

            oStep.Name := 'DTSStep_DTSDataPumpTask';
            oStep.Description := '转换数据任务';
            oStep.ExecutionStatus := DTSStepExecStatus(DTSStepExecStat_Waiting);
            oStep.TaskName := 'DTSTask_DTSDataPumpTask';
            oStep.CommitSuccess := False;
            oStep.RollbackFailure := False;
            oStep.ScriptLanguage := 'VBScript';
            oStep.AddGlobalVariables := True;
            oStep.RelativePriority := DTSStepRelativePriority(DTSStepRelativePriority_Normal);
            oStep.CloseConnection := True;
            oStep.ExecuteInMainThread := False;
            oStep.IsPackageDSORowset := False;
            oStep.JoinTransactionIfPresent := False;
            oStep.DisableStep := False;
            oStep.FailPackageOnError := False;
            
    goPackage.Steps.Add(oStep);
    oStep := nil ;
    
    //---------------------------------------------------------------------------
    // create package tasks information
    //---------------------------------------------------------------------------

    Task_Sub(goPackage);

    //---------------------------------------------------------------------------
    // Save or execute package
    //---------------------------------------------------------------------------

    //goPackage.SaveToSQLServer('localhost','sa','sa',DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,'','','',aaa,false);

    goPackage.Execute;
    tracePackageError(goPackage, Msg);
    goPackage.UnInitialize;

    goPackage := nil;
    goPackageOld := nil;
    
    If Msg = '' Then
       Execute := True
    Else
       Execute := False;
End;

//-----------------------------------------------------------------------------
// error reporting using step.GetExecutionErrorInfo after execution
//-----------------------------------------------------------------------------
procedure TDTSUnit.tracePackageError(const oPackage:Package2 ;var Msg : String);
var ErrorCode,ErrorHelpContext: Longint;
    ErrorSource, ErrorDescription, ErrorHelpFile, ErrorIDofInterfaceWithError: wideString;
    I : Integer;
    StepsOne:Step;
begin
        For I := 1 To oPackage.Steps.Count do
        begin
                StepsOne:=oPackage.Steps.Item(i);
                If StepsOne.ExecutionResult=DTSStepExecResult(DTSStepExecResult_Failure) Then
                begin
                        StepsOne.GetExecutionErrorInfo(ErrorCode, ErrorSource, ErrorDescription,ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError );
                        Msg := Msg + chr(13)+chr(10) + ErrorSource + chr(13)+chr(10) + ErrorDescription;
                End;
        end;
End;

//(转换数据任务: 未定义)
procedure TDTSUnit.Task_Sub(goPackage:Package2);
var
        I : Integer;
        oTask : Task ;
        oCustomTask:customtask;
        oCustomTask1 : DataPumpTask2 ;
begin
        oTask := goPackage.Tasks.New('DTSDataPumpTask');
        oTask.Name := 'DTSTask_DTSDataPumpTask';
        oCustomTask:=  oTask.CustomTask;
        oCustomTask1 := oCustomTask as DataPumpTask2;
        oCustomTask1.Name := 'DTSTask_DTSDataPumpTask';
        oCustomTask1.Description := '转换数据任务';
        oCustomTask1.SourceConnectionID := 1;
        If oCustomTaskValue.oCustomTask1_SourceSQLStatement <> '' Then
        oCustomTask1.SourceSQLStatement := TableName1
        Else
        oCustomTask1.SourceObjectName := TableName1;         //"TABLE1"
        oCustomTask1.DestinationConnectionID := 2;
        oCustomTask1.DestinationObjectName := TableName2;    //"[bs].[dbo].[AccountTbteacher]"
        oCustomTask1.ProgressRowCount := 1000;
        oCustomTask1.MaximumErrorCount := 0;
        oCustomTask1.ExceptionFileName := ErrOutPutFile;
        oCustomTask1.FetchBufferSize := 1;
        oCustomTask1.UseFastLoad := True;
        oCustomTask1.InsertCommitSize := 0;
        oCustomTask1.ExceptionFileColumnDelimiter := '|';
        oCustomTask1.ExceptionFileRowDelimiter :=  #13#10;
        oCustomTask1.AllowIdentityInserts := False;
        oCustomTask1.FirstRow := 0;
        oCustomTask1.LastRow :=  0;
        oCustomTask1.FastLoadOptions := DTSFastLoadOptions(DTSFastLoad_CheckConstraints);
        oCustomTask1.ExceptionFileOptions := DTSExceptionFileOptions(DTSExcepFile_SingleFile70);
        oCustomTask1.DataPumpOptions := 0;

        For I := 1 To ColumnListCount do
        begin
        oCustomTask1_Trans_Sub(oCustomTask1, I);
        end;
        
        goPackage.Tasks.Add(oTask);
        oCustomTask1 := nil;
        oTask := nil;
End;

procedure TDTSUnit.oCustomTask1_Trans_Sub(var oCustomTask1:DataPumpTask2; Index : Integer);
var
        oTransformationOld: Transformation;
        oTransformation : Transformation2;
        oTransProps : Properties;
        oColumn : Column;
begin
        oTransformationOld := oCustomTask1.Transformations.New('DTS.DataPumpTransformCopy');
        oTransformation := oTransformationOld as Transformation2;
                oTransformation.Name := 'DTSTransformation__' + inttostr(Index);
                oTransformation.TransformFlags := 63;
                oTransformation.ForceSourceBlobsBuffered := DTSForceMode(0);
                oTransformation.ForceBlobsInMemory := False;
                oTransformation.InMemoryBlobSize := 1048576;
                oTransformation.TransformPhases := 4;

                oColumn := oTransformation.SourceColumns.New(ColumnList[Index].SourceColumns_Name, 1);
                        oColumn.Name := ColumnList[Index].SourceColumns_Name;
                        oColumn.Ordinal := ColumnList[Index].SourceColumns_Ordinal;
                        oColumn.Flags := ColumnList[Index].SourceColumns_Flags;
                        oColumn.Size := ColumnList[Index].SourceColumns_Size;
                        oColumn.DataType := ColumnList[Index].SourceColumns_DataType;
                        oColumn.Precision := ColumnList[Index].SourceColumns_Precision ;
                        oColumn.NumericScale := ColumnList[Index].SourceColumns_NumericScale;
                        oColumn.Nullable := ColumnList[Index].SourceColumns_Nullable;
                oTransformation.SourceColumns.Add(oColumn);
                oColumn := nil;
                
                oColumn := oTransformation.DestinationColumns.New(ColumnList[Index].DestinationColumns_Name, 1);
                        oColumn.Name := ColumnList[Index].DestinationColumns_Name;
                        oColumn.Ordinal := ColumnList[Index].DestinationColumns_Ordinal;
                        oColumn.Flags := ColumnList[Index].DestinationColumns_Flags;
                        oColumn.Size := ColumnList[Index].DestinationColumns_Size;
                        oColumn.DataType := ColumnList[Index].DestinationColumns_DataType;
                        oColumn.Precision := ColumnList[Index].DestinationColumns_Precision;
                        oColumn.NumericScale := ColumnList[Index].DestinationColumns_NumericScale;
                        oColumn.Nullable := ColumnList[Index].DestinationColumns_Nullable;
                oTransformation.DestinationColumns.Add(oColumn);
                oColumn := nil;

        oTransProps := oTransformation.TransformServerProperties;
        oTransProps := nil;

        oCustomTask1.Transformations.Add(oTransformation);
        oTransformation := nil;
        oTransformationOld := nil;
End;

Function TDTSUnit.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;
begin
       If ColumnListCount >= 100 Then
       begin
          AddColumnList := False;
          Exit;
       End;

       ColumnListCount := ColumnListCount + 1;

       ColumnList[ColumnListCount].SourceColumns_Name := S_Name;
       ColumnList[ColumnListCount].SourceColumns_Ordinal := 1;
       ColumnList[ColumnListCount].SourceColumns_Flags := S_Flags;
       ColumnList[ColumnListCount].SourceColumns_Size := S_Size;
       ColumnList[ColumnListCount].SourceColumns_DataType := S_DataType;
       ColumnList[ColumnListCount].SourceColumns_Precision := S_Precision ;
       ColumnList[ColumnListCount].SourceColumns_NumericScale := S_NumericScale;
       ColumnList[ColumnListCount].SourceColumns_Nullable := S_Nullable;
       
       ColumnList[ColumnListCount].DestinationColumns_Name := D_Name;
       ColumnList[ColumnListCount].DestinationColumns_Ordinal := 1;
       ColumnList[ColumnListCount].DestinationColumns_Flags := D_Flags;
       ColumnList[ColumnListCount].DestinationColumns_Size := D_Size;
       ColumnList[ColumnListCount].DestinationColumns_DataType := D_DataType;
       ColumnList[ColumnListCount].DestinationColumns_Precision := D_Precision;
       ColumnList[ColumnListCount].DestinationColumns_NumericScale := D_NumericScale;
       ColumnList[ColumnListCount].DestinationColumns_Nullable := D_Nullable;

       AddColumnList:=true;
End;

Function TDTSUnit.GetColumnList(ModelFile : String) : Boolean;
var fileStrings:TStringList;
    i:integer;
begin
    fileStrings:=TStringList.Create;
    fileStrings.LoadFromFile(ModelFile);
    for I := 0 to fileStrings.Count - 1 do
    CheckText(Trim(fileStrings.Strings[i]));
    fileStrings.Free;
    GetColumnList := True;
End;

function TDTSUnit.Instr(str: string;substr:string):integer;
begin
    Instr:=Pos(substr,str);
end;

function TDTSUnit.Split(const Source,ch:string):TStringList;
var  temp:String;
     i:Integer;
begin
     Result:=TStringList.Create;
     if Source=''  then exit;
     temp:=Source;
     i:=pos(ch,Source);
     while i<>0 do
     begin
     Result.add(copy(temp,0,i-1));
     Delete(temp,1,i);
     i:=pos(ch,temp);
     end;
     Result.add(temp);
end;


Function TDTSUnit.CheckText(LineText : String) : Boolean;
var tmp : String;
begin