Data Pump Interfaces The Microsoft® SQL Server™ 2000 Data Transformation Services (DTS) data pump is an OLE DB provider that provides the interfaces and methods to import, export, and transform data from an OLE DB data source to an OLE DB destination. The DTS data pump is the engine of the Transform Data task, Data Driven Query task, and Parallel Data Pump task, which is accessed through the ParallelDataPumpTask object.These tasks create rowsets on the source and destination connections, then create an instance of the data pump to move rows between the source and destination rowsets. They also add instances of transformations to the data pump instance, transforming individual row data as it is moved from source to destination.The data pump provides an extensible COM-based architecture that allows custom applications to perform complex data validations and transformations as data moves from source to destination. Third-party applications can also extend the data pump by creating custom COM objects that implement the IDTSDataPumpTransform interface to make use of the full power of Microsoft Win32® and COM. This allows an application to avoid the overhead of converting native data types to OLE DB variant data types and then converting them back again.Data pump interfaces are implemented using Microsoft Visual C++® applications that include Dtspump.h.Using the Data Pump Interfaces An application must call IDTSDataPump interface methods in order. For example, these methods must be called in the following order: SetRowsets AddTransform Execute Other methods on the interface may be called in any order prior to a call to the Execute method. Because the data pump represents a single execute operation, the data pump is not reentrant within calls. It returns an error if it is called as though it were reentrant.Calls to IDTSDataPump result in calls to the IDTSDataPumpTransform interfaces in the following sequence: Create an instance of the transformation server object and associated properties, if necessary. Call IDTSDataPump::AddTransform. Call IPersistPropertyBag::Load, if IPropertyBag is specified. Call IDTSDataPumpTransform::Initialize. Call IDTSDataPumpTransform::AddVariable ("DTSErrorRecords"). Call IDTSDataPumpTransform::ValidateSchema. Call IDTSDataPump::Execute. Call IDTSDataPumpTransform::AddVariable, including any object except DTSErrorRecords. Call IDTSDataPumpTransform::Execute.
The Microsoft® SQL Server™ 2000 Data Transformation Services (DTS) data pump is an OLE DB provider that provides the interfaces and methods to import, export, and transform data from an OLE DB data source to an OLE DB destination. The DTS data pump is the engine of the Transform Data task, Data Driven Query task, and Parallel Data Pump task, which is accessed through the ParallelDataPumpTask object.These tasks create rowsets on the source and destination connections, then create an instance of the data pump to move rows between the source and destination rowsets. They also add instances of transformations to the data pump instance, transforming individual row data as it is moved from source to destination.The data pump provides an extensible COM-based architecture that allows custom applications to perform complex data validations and transformations as data moves from source to destination. Third-party applications can also extend the data pump by creating custom COM objects that implement the IDTSDataPumpTransform interface to make use of the full power of Microsoft Win32® and COM. This allows an application to avoid the overhead of converting native data types to OLE DB variant data types and then converting them back again.Data pump interfaces are implemented using Microsoft Visual C++® applications that include Dtspump.h.Using the Data Pump Interfaces
An application must call IDTSDataPump interface methods in order. For example, these methods must be called in the following order: SetRowsets
AddTransform
Execute
Other methods on the interface may be called in any order prior to a call to the Execute method. Because the data pump represents a single execute operation, the data pump is not reentrant within calls. It returns an error if it is called as though it were reentrant.Calls to IDTSDataPump result in calls to the IDTSDataPumpTransform interfaces in the following sequence: Create an instance of the transformation server object and associated properties, if necessary.
Call IDTSDataPump::AddTransform.
Call IPersistPropertyBag::Load, if IPropertyBag is specified.
Call IDTSDataPumpTransform::Initialize.
Call IDTSDataPumpTransform::AddVariable ("DTSErrorRecords").
Call IDTSDataPumpTransform::ValidateSchema.
Call IDTSDataPump::Execute.
Call IDTSDataPumpTransform::AddVariable, including any object except DTSErrorRecords.
Call IDTSDataPumpTransform::Execute.
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
//package1.Execute;
//CoInitialize;
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.Steps.New;
dts_task:=opackage.Tasks.New('DTSDataPumpTask');
dts_task.Name:='Copy Data from 1.mdb to 2.mdb Task';
dts_customtask:=dts_task.CustomTask;
dts_pumptask:=dts_customtask as datapumptask2;
dts_pumptask.Name:='Copy Data from 1.mdb to 2.mdb Task';
dts_pumptask.SourceConnectionID:=1;
dts_pumptask.SourceSQLStatement:='select * from table1';
//dts_pumptask.SourceObjectName:='table1';
dts_pumptask.DestinationConnectionID:=2;
dts_pumptask.SourceSQLStatement:='select * from table1';
//dts_pumptask.DestinationObjectName:='table1';
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;小弟只能帮你这么多,多了我也不会了,哈!
var opackage: _package;
ostep :step;
otask :task;
ocusttask :executepackagetask;
begin
opackage:=CreateOleObject('DTS.Package') as _package;
ostep:=opackage.steps.new;
ostep.name:='execpkgstep';
otask:=opackage.tasks.new('dtsexecutepackagetask');
ocusttask:=otask.customtask as executepackagetask;
ocusttask.name:='execpkgtask';
ostep.taskname:=ocusttask.name;
opackage.steps.add(ostep);
ostep:=nil;
ocusttask.filename:='dts_a11.dts';
opackage.tasks.add(otask);
otask:=nil;
opackage.execute;
opackage.UnInitialize;
end;
我想大概是应该创建一下OBJ吗?
谢谢哥们，你的代码是调用DTS的，这个我早就调通了。现在主要是DTS编程，哎，头疼啊！
丢几个连接和任务，执行一下，通过后（满足你的需求后）另存为
Visual Basic 文件。用记事本打开。然后放在delphi力改改就可以了，速度很快。/////////////////////////////////////////////////////////////////////////////////
下面是相关资料,不知道是否能用的上!
procedure Task_CopyData_Inventory(goPackage:Variant;stationid:String);
procedure CustomTask2_Trans_Inventory(oCustomTask2:Variant);
function ExportInventoryToMdb_DTS(stationid:String;filename:String;TableName:String):Boolean;
procedure Task_CreateTable_Order(goPackage:Variant);
procedure Task_CopyData_Order(goPackage:Variant;stationid:String);
procedure CustomTask2_Trans_Order(oCustomTask2:Variant);
function ExportOrderToMdb_DTS(stationid:String;filename:String;TableName:String):Boolean;implementationuses pubfunc;function ExportInventoryToMdb_DTS(stationid:String;filename:String;TableName:String):Boolean;
var
goPackageOld,goPackage,oConnection,oStep,oPrecConstraint:Variant;
begin
goPackageOld:=CreateOleObject('DTS.Package');
goPackage:=CreateOleObject('DTS.Package2'); goPackage := goPackageOld; goPackage.Name := 'ExportInverntory';
goPackage.Description := 'µ¼³ö¿â´æÊý¾Ý';
goPackage.WriteCompletionStatusToNTEventLog := False;
goPackage.FailOnError := True;
goPackage.PackagePriorityClass := 2;
goPackage.MaxConcurrentSteps := 4;
goPackage.LineageOptions := 0;
goPackage.UseTransaction := True;
goPackage.TransactionIsolationLevel := 4096;
goPackage.AutoCommitTransaction := True;
goPackage.RepositoryMetadataOptions := 0;
goPackage.UseOLEDBServiceComponents := True;
goPackage.LogToSQLServer := False;
goPackage.LogServerFlags := 0;
goPackage.FailPackageOnLogFailure := False;
goPackage.ExplicitGlobalVariables := False;
goPackage.PackageType := 0; oConnection := goPackage.Connections.New('SQLOLEDB'); oConnection.Name := 'Á¬½Ó1';
oConnection.ID := 1;
oConnection.Reusable := True;
oConnection.ConnectImmediate := False;
oConnection.DataSource := gSqlSrv;
oConnection.UserID := gSqlUser;
oConnection.Password := gSqlPassword;
oConnection.ConnectionTimeout := 60;
oConnection.Catalog := gSqlDatabase;
oConnection.UseTrustedConnection := False;
oConnection.UseDSL := False; goPackage.Connections.Add(oConnection);
oConnection :=unassigned; oConnection := goPackage.Connections.New('Microsoft.Jet.OLEDB.4.0'); oConnection.Name := 'Á¬½Ó2';
oConnection.ID := 2;
oConnection.Reusable := True;
oConnection.ConnectImmediate := False;
oConnection.DataSource := filename;
oConnection.ConnectionTimeout := 60;
oConnection.UseTrustedConnection := False;
oConnection.UseDSL := False; goPackage.Connections.Add(oConnection);
oConnection :=unassigned; //------------- a new step defined below oStep := goPackage.Steps.New; oStep.Name := '´´½¨±í Inventory ²½Öè';
oStep.Description := '´´½¨±í Inventory ²½Öè';
oStep.ExecutionStatus := 1;
oStep.TaskName := '´´½¨±í Inventory ÈÎÎñ';
oStep.CommitSuccess := False;
oStep.RollbackFailure := False;
oStep.ScriptLanguage := 'VBScript';
oStep.AddGlobalVariables := True;
oStep.RelativePriority := 3;
oStep.CloseConnection := False;
oStep.ExecuteInMainThread := False;
oStep.IsPackageDSORowset := False;
oStep.JoinTransactionIfPresent := False;
oStep.DisableStep := False;
oStep.FailPackageOnError := False; goPackage.Steps.Add(oStep);
oStep := unassigned; // ------------- a new step defined below oStep := goPackage.Steps.New; oStep.Name := 'Copy Data from Inventory to Inventory ²½Öè';
oStep.Description := 'Copy Data from Inventory to Inventory ²½Öè';
oStep.ExecutionStatus := 1;
oStep.TaskName := 'Copy Data from Inventory to Inventory ÈÎÎñ';
oStep.CommitSuccess := False;
oStep.RollbackFailure := False;
oStep.ScriptLanguage := 'VBScript';
oStep.AddGlobalVariables := True;
oStep.RelativePriority := 3;
oStep.CloseConnection := False;
oStep.ExecuteInMainThread := True;
oStep.IsPackageDSORowset := False;
oStep.JoinTransactionIfPresent := False;
oStep.DisableStep := False;
oStep.FailPackageOnError := False; goPackage.Steps.Add(oStep); //------------- a precedence constraint for steps defined below oPrecConstraint := oStep.PrecedenceConstraints.New('´´½¨±í Inventory ²½Öè');
oPrecConstraint.StepName := '´´½¨±í Inventory ²½Öè';
oPrecConstraint.PrecedenceBasis := 0;
oPrecConstraint.Value := 4; oStep.precedenceConstraints.Add(oPrecConstraint);
oPrecConstraint := unassigned; Task_CreateTable_Inventory(goPackage);
Task_CopyData_Inventory(goPackage,stationid); goPackage.Execute;
goPackage.Uninitialize; goPackageOld:=unassigned;
goPackage:=unassigned;
Result:=True;
end;