一个自定义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"
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