如何用DTS编程实现两个ms sql server 间表的备份

[whh]

如何用DTS编程实现两个ms sql server 间表的备份

[华南虎哥]

不知道使用delphi自带的控件可否进行, 不凡试试

[gxdq]

guan zhu

[wangxd1976]

哥们，问题解决了吗？如果已经解决，指点指点我。

[hydnoahark]

一个自定义DTS的例子（VB）:
测试环境：VB6.0+SQL Server 7.0
源表：Account_Code
CREATE TABLE [Account_Code] (
[Acct_Code] [nvarchar] (255) NOT NULL ,
[Description] [nvarchar] (255) NOT NULL
)
备份表：Account_Code_BAK
CREATE TABLE [Account_Code_BAK] (
[Acct_Code] [nvarchar] (255) NOT NULL ,
[Description] [nvarchar] (255) NOT NULL
)

在VB中新建一个Standard Exe工程,引用Microsoft DTSDataPump Scripting Object Library和Microsoft DTSPackage Object Library:
Private Sub Command1_Click()
Dim oPackage As New DTS.Package
Dim oConnection As DTS.Connection
Dim oTask As DTS.Task
Dim oStep As DTS.Step
Dim oTransform As DTS.Transformation
Dim oPumpTask As DTS.DataPumpTask
Dim oProps As DTS.Properties
Dim iStatus As Boolean
Dim i As Integer
Dim strODBCdsn As String
Dim strUserName As String
Dim strPassword As String


oPackage.Name = "DTS Simple Package"
oPackage.Description = "Example of a simple DTS package"


strODBCdsn = "nwt_bak"
strUserName = "sa"
strPassword = ""
Set oConnection = oPackage.Connections.New
oConnection.ID = 1
oConnection.DataSource = strODBCdsn
oConnection.UserID = strUserName
oConnection.Password = strPassword
oPackage.Connections.Add oConnection
Set oConnection = Nothing

Set oConnection = oPackage.Connections.New
oConnection.ID = 2
oConnection.DataSource = strODBCdsn
oConnection.UserID = strUserName
oConnection.Password = strPassword
oPackage.Connections.Add oConnection
Set oConnection = Nothing

Set oTask = oPackage.Tasks.New("DTSDataPumpTask")
oTask.Name = "Task1"
Set oPumpTask = oTask.CustomTask
oPumpTask.SourceConnectionID = 1

oPumpTask.SourceSQLStatement = "Select acct_code,description from nwt_bak..account_code"
oPumpTask.DestinationConnectionID = 2

oPumpTask.DestinationObjectName = "account_code_bak"

Set oTransform = oPumpTask.Transformations.New("DTS.DataPumpTransformCopy")
oTransform.Name = "Transform"
oTransform.TransformFlags = DTSTransformFlag_AllowLosslessConversion
oPumpTask.Transformations.Add oTransform
oPackage.Tasks.Add oTask
Set oTask = Nothing
Set oStep = oPackage.Steps.New
oStep.Name = "Step1"
oStep.TaskName = "Task1"
oPackage.Steps.Add oStep
Set oStep = Nothing

oPackage.Execute

iStatus = True
For i = 1 To oPackage.Steps.Count
If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
iStatus = False
MsgBox oPackage.Steps(i).Name + " in the " + oPackage.Description + " failed."
End If
Next i
If iStatus = True Then
MsgBox oPackage.Description + " Successful"
End If
Set oPackage = Nothing
End Sub

[Jazzlover]

www.microsoft.com上有这类代码。