dts.~pas

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

    End;
       
    If GetColumnList(ModelFile) = False Then
    begin
        Msg := '模板不能初始化';
        Execute := False;
        Exit;
    End;
    
    If (ColumnListCount = 0) Then
    begin
        Msg := '字段对应关系没有初始化';
        Execute := False;
        Exit;
    End;

    Desindex:=0;

    //goPackageOld := PackageClass.Create();
    goPackageOld:=CreateOLEObject('dts.Package') as Package;
    goPackage := goPackageOld as Package2;

    //goPackage.OnProgress:=OnProgress;

   	goPackage.Name := 'InsertDepart';
   	goPackage.WriteCompletionStatusToNTEventLog := False;
   	goPackage.FailOnError := False;
  	goPackage.PackagePriorityClass := DTSPackagePriorityClass(DTSPriorityClass_Normal);
  	goPackage.MaxConcurrentSteps := 4;
        goPackage.LineageOptions := DTSLineageOptions(DTSLineage_None);
        goPackage.UseTransaction := True;
   	goPackage.TransactionIsolationLevel := DTSIsolationLevel(DTSIsoLevel_ReadCommitted);
  	goPackage.AutoCommitTransaction := True;
  	goPackage.RepositoryMetadataOptions := DTSRepositoryMetadataOptions(DTSReposMetadata_Default);
  	goPackage.UseOLEDBServiceComponents := True;
	goPackage.LogToSQLServer := False;
  	goPackage.LogServerFlags := DTSSQLServerStorageFlags(DTSSQLStgFlag_Default);
  	goPackage.FailPackageOnLogFailure := False;
        goPackage.ExplicitGlobalVariables := False;
        goPackage.PackageType := DTSPackageType(DTSPkgType_Default);
	
    //---------------------------------------------------------------------------
    // create package connection information
    //---------------------------------------------------------------------------

    If InitializeFromMode = 1 Then
    begin
            oConnection := goPackage.Connections.New('DTSFlatFile');

            If oCustomTaskValue.oCustomTask1_SourceConnectionID = DTSFlatFile[2].oConnection_ID Then Desindex := 2;
            If oCustomTaskValue.oCustomTask1_SourceConnectionID = DTSFlatFile[3].oConnection_ID Then Desindex := 3;
            If Desindex = 0 Then
            begin
                 Msg := '导入文本类型，这与模板文件不一致。';
                 Execute:=false;
                 Exit;
            end;

            oConnection.ConnectionProperties.Item('Data Source').Value:= DataFile1;
            oConnection.ConnectionProperties.Item('Mode').Value:= DTSFlatFile[Desindex].oConnection_ConnectionProperties_Mode;
            oConnection.ConnectionProperties.Item('Row Delimiter').Value:= #13#10;
            oConnection.ConnectionProperties.Item('File Format').Value:= (DTSFlatFile[Desindex].oConnection_ConnectionProperties_FileFormat);
            oConnection.ConnectionProperties.Item('Column Delimiter').Value:= DTSFlatFile[Desindex].oConnection_ConnectionProperties_ColumnDelimiter;
            oConnection.ConnectionProperties.Item('File Type').Value:= DTSFlatFile[Desindex].oConnection_ConnectionProperties_FileType;
            oConnection.ConnectionProperties.Item('Skip Rows').Value:= (DTSFlatFile[Desindex].oConnection_ConnectionProperties_SkipRows);
            oConnection.ConnectionProperties.Item('Text Qualifier').Value:= DTSFlatFile[Desindex].oConnection_ConnectionProperties_TextQualifierColMask;
            oConnection.ConnectionProperties.Item('First Row Column Name').Value:= (DTSFlatFile[Desindex].oConnection_ConnectionProperties_FirstRowColumnName);
            oConnection.ConnectionProperties.Item('Number of Column').Value:= (DTSFlatFile[Desindex].oConnection_ConnectionProperties_NumberofColumn);
            oConnection.ConnectionProperties.Item('Max characters per delimited column').Value:= (DTSFlatFile[Desindex].oConnection_ConnectionProperties_Maxcharactersperdelimitedcolumn);

            oConnection.Name := 'Text File (Source) 1';
            oConnection.ID := 1;
            oConnection.Reusable := True;
            oConnection.ConnectImmediate := False;
            oConnection.DataSource := DataFile1;
            oConnection.ConnectionTimeout := 60;
            oConnection.UseTrustedConnection := False;
            oConnection.UseDSL := False;
        
            goPackage.Connections.Add(oConnection);
            oConnection := nil;
    end
    Else If InitializeFromMode = 2 Then
    begin
            oConnection := goPackage.Connections.New('Microsoft.Jet.OLEDB.4.0');
        
            oConnection.ConnectionProperties.Item('User ID').Value:= 'Admin';
            oConnection.ConnectionProperties.Item('Data Source').Value := DataFile1;
            oConnection.ConnectionProperties.Item('Extended Properties').Value := 'Excel 8.0;HDR=YES;';

            oConnection.Name := 'Microsoft Excel 97-2000 1';
            oConnection.ID := 1;
            oConnection.Reusable := True;
            oConnection.ConnectImmediate := False;
            oConnection.DataSource := DataFile1;
            oConnection.UserID := 'Admin';
            oConnection.ConnectionTimeout := 60;
            oConnection.UseTrustedConnection := False;
            oConnection.UseDSL := False;

            goPackage.Connections.Add(oConnection);
            oConnection := nil;
    end
    Else If InitializeFromMode = 3 Then
    begin
            oConnection := goPackage.Connections.New('Microsoft.Jet.OLEDB.4.0');
        
            oConnection.ConnectionProperties.Item('User ID').Value := 'Admin';
            oConnection.ConnectionProperties.Item('Data Source').Value := DataFile1;

            oConnection.Name := 'Microsoft Access 1';
            oConnection.ID := 1;
            oConnection.Reusable := True;
            oConnection.ConnectImmediate := False;
            oConnection.DataSource := DataFile1;
            oConnection.UserID := 'Admin';
            oConnection.ConnectionTimeout := 60;
            oConnection.UseTrustedConnection := False;
            oConnection.UseDSL := False;

            goPackage.Connections.Add(oConnection);
            oConnection := nil;
    end
    Else If InitializeFromMode = 4 Then
    begin
                oConnection := goPackage.Connections.New('SQLOLEDB');

                oConnection.ConnectionProperties.Item('Persist Security Info').Value := True;
                oConnection.ConnectionProperties.Item('User ID').Value := Username1;
                oConnection.ConnectionProperties.Item('Password').Value := Password1;
                oConnection.ConnectionProperties.Item('Initial Catalog').Value := DBname1;
                oConnection.ConnectionProperties.Item('Data Source').Value := SQLserverIP1;
                oConnection.ConnectionProperties.Item('Application Name').Value := 'DTS 设计器';

                oConnection.Name := 'Microsoft OLE DB Provider for SQL Server 1';
                oConnection.ID := 1;
                oConnection.Reusable := True;
                oConnection.ConnectImmediate := False;
                oConnection.DataSource := SQLserverIP1;
                oConnection.UserID := Username1;
                oConnection.Password := Password1;
                oConnection.ConnectionTimeout := 60;
                oConnection.Catalog := DBname1;
                oConnection.UseTrustedConnection := False;
                oConnection.UseDSL := False;

                goPackage.Connections.Add(oConnection);
                oConnection := nil;
    end
    Else If InitializeFromMode = 5 Then
    begin
                oConnection := goPackage.Connections.New('MSDASQL');

                oConnection.ConnectionProperties.Item('Persist Security Info').Value := True;
                oConnection.ConnectionProperties.Item('Data Source').Value := DBname1;
                
                oConnection.Name := 'MySQL ODBC 3.51 Driver';
                oConnection.ID := 1;
                oConnection.Reusable := True;
                oConnection.ConnectImmediate := False;
                oConnection.DataSource := DBname1;
                oConnection.ConnectionTimeout := 60;
                oConnection.UseTrustedConnection := False;
                oConnection.UseDSL := False;

                goPackage.Connections.Add(oConnection);
                oConnection := nil;
    End;
    
    
    If InitializeToMode = 1 Then
    begin
            oConnection := goPackage.Connections.New('DTSFlatFile');
            If oCustomTaskValue.oCustomTask1_DestinationConnectionID = DTSFlatFile[2].oConnection_ID Then Desindex := 2;
            If oCustomTaskValue.oCustomTask1_DestinationConnectionID = DTSFlatFile[3].oConnection_ID Then Desindex := 3;
            If Desindex = 0 Then
            begin
                 Msg := '导出文本类型，这与模板文件不一致。';
                 Execute:=false;
                 Exit;
            end;
            
            oConnection.ConnectionProperties.Item('Data Source').Value := DataFile2;
            oConnection.ConnectionProperties.Item('Mode').Value := DTSFlatFile[Desindex].oConnection_ConnectionProperties_Mode;
            oConnection.ConnectionProperties.Item('Row Delimiter').Value := #13#10;
            oConnection.ConnectionProperties.Item('File Format').Value := DTSFlatFile[Desindex].oConnection_ConnectionProperties_FileFormat;
            oConnection.ConnectionProperties.Item('Column Lengths').Value := DTSFlatFile[Desindex].oConnection_ConnectionProperties_ColumnLengths;
            oConnection.ConnectionProperties.Item('Column Delimiter').Value := DTSFlatFile[Desindex].oConnection_ConnectionProperties_ColumnDelimiter;
            oConnection.ConnectionProperties.Item('File Type').Value := DTSFlatFile[Desindex].oConnection_ConnectionProperties_FileType;
            oConnection.ConnectionProperties.Item('Skip Rows').Value := DTSFlatFile[Desindex].oConnection_ConnectionProperties_SkipRows;
            oConnection.ConnectionProperties.Item('First Row Column Name').Value := DTSFlatFile[Desindex].oConnection_ConnectionProperties_FirstRowColumnName;
            oConnection.ConnectionProperties.Item('Column Names').Value := DTSFlatFile[Desindex].oConnection_ConnectionProperties_ColumnNames;
            oConnection.ConnectionProperties.Item('Number of Column').Value := DTSFlatFile[Desindex].oConnection_ConnectionProperties_NumberofColumn;
            oConnection.ConnectionProperties.Item('Text Qualifier Col Mask: 0=no, 1=yes, e.g. 0101').Value := DTSFlatFile[Desindex].oConnection_ConnectionProperties_TextQualifierColMask;
            oConnection.ConnectionProperties.Item('Max characters per delimited column').Value := DTSFlatFile[Desindex].oConnection_ConnectionProperties_Maxcharactersperdelimitedcolumn;
            oConnection.ConnectionProperties.Item('Blob Col Mask: 0=no, 1=yes, e.g. 0101').Value := DTSFlatFile[Desindex].oConnection_ConnectionProperties_BlobColMask;
            
            oConnection.Name := 'Text File (Destination) 2';
            oConnection.ID := 2;
            oConnection.Reusable := True;
            oConnection.ConnectImmediate := False;
            oConnection.DataSource := DataFile2;
            oConnection.ConnectionTimeout := 60;
            oConnection.UseTrustedConnection := False;
            oConnection.UseDSL := False;

            goPackage.Connections.Add(oConnection);
            oConnection := nil;
    end
    Else If InitializeToMode = 2 Then
    begin
            oConnection := goPackage.Connections.New('Microsoft.Jet.OLEDB.4.0');
        
            oConnection.ConnectionProperties.Item('User ID').Value := 'Admin';
            oConnection.ConnectionProperties.Item('Data Source').Value := DataFile2;
            oConnection.ConnectionProperties.Item('Extended Properties').Value := 'Excel 8.0;HDR=YES;';
            
            oConnection.Name := 'Microsoft Excel 97-2000 2';
            oConnection.ID := 2;
            oConnection.Reusable := True;
            oConnection.ConnectImmediate := False;
            oConnection.DataSource := DataFile2;
            oConnection.UserID := 'Admin';
            oConnection.ConnectionTimeout := 60;
            oConnection.UseTrustedConnection := False;
            oConnection.UseDSL := False;
        
            goPackage.Connections.Add(oConnection);
            oConnection := nil;
    end
    Else If InitializeToMode = 3 Then
    begin
            oConnection := goPackage.Connections.New('Microsoft.Jet.OLEDB.4.0');
        
            oConnection.ConnectionProperties.Item('User ID').Value := 'Admin';
            oConnection.ConnectionProperties.Item('Data Source').Value := DataFile2;

            oConnection.Name := 'Microsoft Access 2';
            oConnection.ID := 2;
            oConnection.Reusable := True;
            oConnection.ConnectImmediate := False;
            oConnection.DataSource := DataFile2;
            oConnection.UserID := 'Admin';
            oConnection.ConnectionTimeout := 60;
            oConnection.UseTrustedConnection := False;
            oConnection.UseDSL := False;
        
            goPackage.Connections.Add(oConnection);
            oConnection := nil;
    end
    Else If InitializeToMode = 4 Then
    begin
                oConnection := goPackage.Connections.New('SQLOLEDB');

                oConnection.ConnectionProperties.Item('Persist Security Info').Value := True;
                oConnection.ConnectionProperties.Item('User ID').Value := Username2;
                oConnection.ConnectionProperties.Item('Password').Value := Password2;
                oConnection.ConnectionProperties.Item('Initial Catalog').Value := DBname2;
                oConnection.ConnectionProperties.Item('Data Source').Value := SQLserverIP2;
                oConnection.ConnectionProperties.Item('Application Name').Value := 'DTS 设计器';

                oConnection.Name := 'Microsoft OLE DB Provider for SQL Server 2';
                oConnection.ID := 2;
                oConnection.Reusable := True;
                oConnection.ConnectImmediate := False;
                oConnection.DataSource := SQLserverIP2;