小弟是个新手,公司最近让我做数据维护系统,靠!老大给我说了一声用DTS做,
我一个刚毕业的学生,怎么懂啊,无耐只能请教各位高手,可以的话,请留下你们的QQ,大家交流一下
我一个刚毕业的学生,怎么懂啊,无耐只能请教各位高手,可以的话,请留下你们的QQ,大家交流一下
解决方案 »
- 如何获取ComboBox下拉框处在打开状态的值?
- 查找1~100中缺少的数字
- 为什么下面这段代码在VS2005下没问题 在VB6下有问题?
- 瑞星计算MD5的数值为什么和这段VB代码计算结果不一致?
- movenext为何这么慢
- 高手请教有关用VB开发WEB方面的基本问题
- 50分急求! 如何使程序能自动获取自己所在的路径!~谢谢~
- 在线急等:winsock做的小程序考别人机子上怎么就打不开呢?
- 如何检测串口的数量,和某个串口是否已被占用?
- !!请大家帮忙!!
- 使用CreateObject("SrcObject")建立的对象,怎么才能使用SrcObject的事件。
- DataGrid控件如何选中某一行?
在DMO中有很多对象,比如说SQLDMO.SQLServer2\SQLDMO.NameList\SQLDMO.Database...
还有你可以在SQLSERVER中添加DTS包,保存为vb文件,里面有你想要的东西
要不你就看看MSDN,上边有很多例子,不过都是英文的...我学的好费经了....给你一个例子吧,如何在vb中创建DTS包:'***************************************************
'功能:创建DTS包
'参数:无
'返回:无
'***************************************************Public Function CreateDTS() As Boolean Dim i As Integer
Dim goPackage As DTS.Package2 'DTC包
Dim goPackageOld As New DTS.Package
Set goPackage = goPackageOld
'
With goPackage
.Name = "His系统自动任务"
.Description = "His系统自动任务"
.WriteCompletionStatusToNTEventLog = True
.LogFileName = "C:\DTSError.txt"
.FailOnError = False
.PackagePriorityClass = 2
.MaxConcurrentSteps = 1
.LineageOptions = 0
.UseTransaction = True
.TransactionIsolationLevel = 4096
.AutoCommitTransaction = True
.RepositoryMetadataOptions = 0
.UseOLEDBServiceComponents = True
.LogToSQLServer = True
.LogServerName = mvarDataBaseName
.LogServerUserName = mvarDataUID
.LogServerPassword = mvarDataPassWord
.LogServerFlags = 0
.FailPackageOnLogFailure = False
.ExplicitGlobalVariables = False
.PackageType = 0
End With Call CreateConnect(goPackage)
For i = 1 To objCreateTask.Count
If objCreateTask.Item(i).IsCreated = True Then
Call CreateTask(goPackage, objCreateTask.Item(i).TaskName, objCreateTask.Item(i).TaskDescription)
Call Task_Sub(goPackage, objCreateTask.Item(i).TaskName, objCreateTask.Item(i).TaskDescription, objCreateTask.Item(i).TaskCommand, objCreateTask.Item(i).TaskType)
End If
Next i
On Error Resume Next
goPackage.RemoveFromSQLServer mvarDataBaseName, mvarDataUID, mvarDataPassWord, , , , "His系统自动任务"
goPackage.SaveToSQLServer mvarDataBaseName, mvarDataUID, mvarDataPassWord
Call CreateJob
CreateDTS = True
Set goPackage = Nothing
Set goPackageOld = Nothing
Exit Function
ErrorLine:
CreateDTS = False
Set goPackage = Nothing Set goPackageOld = NothingEnd Function
'****************************************
' 功能:建立DTC包连接信息
' 参数:
'*****************************************
Private Sub CreateConnect(ByVal goPackage As Object) Dim oConnection As DTS.Connection2 Set oConnection = goPackage.Connections.New("SQLOLEDB")
With oConnection
.ConnectionProperties("Persist Security Info") = True
.ConnectionProperties("User ID") = mvarDataUID
.ConnectionProperties("Initial Catalog") = mvarDTSDataBase
.ConnectionProperties("Data Source") = mvarDataBaseName
.ConnectionProperties("Application Name") = "DTS 设计器"
.ConnectionProperties("Password") = mvarDataPassWord
.Name = "Microsoft OLE DB Provider for SQL Server"
.ID = 1
.Reusable = True
.ConnectImmediate = False
.DataSource = mvarDataBaseName
.UserID = mvarDataUID
.Password = mvarDataPassWord
.ConnectionTimeout = 60
.Catalog = mvarDTSDataBase
.UseTrustedConnection = False
.UseDSL = False
End With
goPackage.Connections.Add oConnection
Set oConnection = NothingEnd Sub'创建自动任务
Private Sub CreateTask(ByVal goPackage As Object, ByVal strTaskName As String, ByVal strTaskDescription As String)
Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint
Set oStep = goPackage.Steps.New oStep.Name = strTaskName
oStep.Description = strTaskDescription
oStep.ExecutionStatus = 1
oStep.TaskName = strTaskName
oStep.CommitSuccess = True
oStep.RollbackFailure = True
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
Set oStep = Nothing
End Sub
'******************************
'功能:添加DTS包作业
'参数:
'返回:
'********************************
Public Sub Task_Sub(ByVal goPackage As Object, ByVal strTaskName As String, ByVal strDescr As String, ByVal strTaskCommand As String, ByVal lngTaskType As Long) Dim oTask As DTS.Task
Dim oTask1 As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.CreateProcessTask2
Dim oCustomTask As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask = oTask.CustomTask
Set oTask1 = goPackage.Tasks.New("DTSCreateProcessTask")
Set oCustomTask1 = oTask1.CustomTask
Select Case lngTaskType
Case TaskType.CopyDataBaseObject
oCustomTask.Name = strTaskName
oCustomTask.Description = strDescr
oCustomTask.SQLStatement = strTaskCommand & "'" & mvarChangeDataName & "'," & mvarChangeDays
oCustomTask.CommandTimeout = 3600
oCustomTask.OutputAsRecordset = False
goPackage.Tasks.Add oTask
Case TaskType.Procedure
oCustomTask.Name = strTaskName
oCustomTask.Description = strDescr
oCustomTask.SQLStatement = strTaskCommand
oCustomTask.ConnectionID = 1
oCustomTask.CommandTimeout = 3600
oCustomTask.OutputAsRecordset = False
goPackage.Tasks.Add oTask
Case TaskType.ProcessCommand
oCustomTask1.Name = strTaskName
oCustomTask1.Description = strDescr
oCustomTask1.ProcessCommandLine = strTaskCommand
oCustomTask1.SuccessReturnCode = 0
oCustomTask1.SuccessReturnCode = 0
oCustomTask1.Timeout = 3600
oCustomTask1.TerminateProcessAfterTimeout = True
oCustomTask1.FailPackageOnTimeout = True
goPackage.Tasks.Add oTask1
End Select
Set oCustomTask = Nothing
Set oTask = Nothing
Set oCustomTask1 = Nothing
Set oTask1 = Nothing
End Sub