📄 form1.frm
字号:
VERSION 5.00
Object = "{08B0B2E5-3FB3-11D3-A4DE-00C04F610189}#1.0#0"; "sqldistx.dll"
Object = "{0ECD9B60-23AA-11D0-B351-00A0C9055D8E}#6.0#0"; "MSHFLXGD.OCX"
Begin VB.Form Form1
Caption = "Oracle数据库到SQL SERVER 数据库DTS系统"
ClientHeight = 9840
ClientLeft = 60
ClientTop = 345
ClientWidth = 11715
LinkTopic = "Form1"
MaxButton = 0 'False
MinButton = 0 'False
ScaleHeight = 9840
ScaleWidth = 11715
StartUpPosition = 3 '窗口缺省
Begin VB.ListBox SQLScriptList
Height = 3660
Left = 7710
TabIndex = 25
Top = 5910
Visible = 0 'False
Width = 2955
End
Begin MSHierarchicalFlexGridLib.MSHFlexGrid FlxGrid1
Height = 2895
Left = 240
TabIndex = 22
Top = 6660
Width = 11175
_ExtentX = 19711
_ExtentY = 5106
_Version = 393216
_NumberOfBands = 1
_Band(0).Cols = 2
End
Begin VB.Frame Frame4
Caption = "Frame4"
Height = 4095
Left = 240
TabIndex = 20
Top = 2490
Width = 11175
Begin VB.ListBox List2
Height = 3660
Left = 2940
TabIndex = 24
Top = 240
Width = 2955
End
Begin VB.ListBox List1
Height = 3660
Left = 180
TabIndex = 23
Top = 270
Width = 2325
End
Begin MSHierarchicalFlexGridLib.MSHFlexGrid PropGrid
Height = 3675
Left = 6060
TabIndex = 21
Top = 330
Width = 5025
_ExtentX = 8864
_ExtentY = 6482
_Version = 393216
_NumberOfBands = 1
_Band(0).Cols = 2
End
End
Begin VB.Frame Frame3
Caption = "Control Pane"
Height = 795
Left = 300
TabIndex = 14
Top = 1620
Width = 11115
Begin VB.CheckBox Check1
Caption = "显示数据"
Height = 195
Left = 420
TabIndex = 19
Top = 420
Value = 1 'Checked
Width = 1245
End
Begin VB.CommandButton Command4
Caption = "连接Ora"
Height = 375
Left = 5400
TabIndex = 18
Top = 270
Width = 1395
End
Begin VB.CommandButton Command3
Caption = "定时调度作业"
Height = 375
Left = 8220
TabIndex = 17
Top = 270
Width = 1395
End
Begin VB.CommandButton GenDTSPackage
Caption = "生成中间件"
Height = 375
Left = 6810
TabIndex = 16
Top = 270
Width = 1395
End
Begin VB.CommandButton Command1
Caption = "EXIT"
Height = 375
Left = 9630
TabIndex = 15
Top = 270
Width = 1395
End
End
Begin VB.Frame Frame2
Caption = "SQL 属性"
Height = 1395
Left = 9060
TabIndex = 7
Top = 90
Width = 2295
Begin VB.TextBox Text6
Height = 270
Left = 1050
TabIndex = 10
Top = 240
Width = 1095
End
Begin VB.TextBox Text5
Height = 270
IMEMode = 3 'DISABLE
Left = 1050
PasswordChar = "*"
TabIndex = 9
Top = 600
Width = 1095
End
Begin VB.TextBox Text4
Height = 270
Left = 1050
TabIndex = 8
Top = 960
Width = 1095
End
Begin VB.Label Label6
Caption = "UserName"
Height = 255
Left = 120
TabIndex = 13
Top = 240
Width = 735
End
Begin VB.Label Label5
Caption = "PWD"
Height = 255
Left = 120
TabIndex = 12
Top = 600
Width = 735
End
Begin VB.Label Label4
Caption = "DB"
Height = 255
Left = 120
TabIndex = 11
Top = 960
Width = 735
End
End
Begin VB.Frame Frame1
Caption = "Oracle 属性"
Height = 1395
Left = 300
TabIndex = 0
Top = 120
Width = 2295
Begin VB.TextBox Text3
Height = 270
Left = 1050
TabIndex = 6
Text = "CRM7PES"
Top = 960
Width = 1095
End
Begin VB.TextBox Text2
Height = 270
IMEMode = 3 'DISABLE
Left = 1050
PasswordChar = "*"
TabIndex = 4
Text = "qmserver"
Top = 600
Width = 1095
End
Begin VB.TextBox Text1
Height = 270
Left = 1050
TabIndex = 2
Text = "CTS"
Top = 240
Width = 1095
End
Begin VB.Label Label3
Caption = "DB"
Height = 255
Left = 120
TabIndex = 5
Top = 960
Width = 735
End
Begin VB.Label Label2
Caption = "PWD"
Height = 255
Left = 120
TabIndex = 3
Top = 600
Width = 735
End
Begin VB.Label Label1
Caption = "UserName"
Height = 255
Left = 120
TabIndex = 1
Top = 240
Width = 735
End
End
Begin SQLDISTXLibCtl.SQLDistribution SQLDistribution1
Height = 180
Left = 4170
OleObjectBlob = "Form1.frx":0000
TabIndex = 26
Top = 480
Width = 255
End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'1) 在整个程序运行过程中，需要两个全局变量：DTS 包 oPackage 和Oracle 服务器连接 oraCon。
'当设置好连接参数后，单击“连接Oracle服务器”，将与 ORACLE 服务器连接，并取出所有表。
'然后填充第一个列表框，并在下面表格中显示相应的数据（如果选中了“显示数据”）
Dim oraCon As New ADODB.Connection
Dim oPackage As New DTS.Package
Dim oPackageOld As New DTS.Package
Private Sub GenTaskAgent()
Dim strCmd As String
strCmd = ""
strCmd = strCmd & " BEGIN TRANSACTION" & vbCrLf
strCmd = strCmd & "DECLARE @JobID BINARY(16)" & vbCrLf
strCmd = strCmd & "DECLARE @ReturnCode INT" & vbCrLf
strCmd = strCmd & "SELECT @ReturnCode = 0" & vbCrLf
strCmd = strCmd & "IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1" & vbCrLf
strCmd = strCmd & "EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'" & vbCrLf
strCmd = strCmd & "SELECT @JobID = job_id" & vbCrLf
strCmd = strCmd & "From msdb.dbo.sysjobs" & vbCrLf
strCmd = strCmd & "WHERE (name = N'OraToSQL')" & vbCrLf
strCmd = strCmd & "IF (@JobID IS NOT NULL)" & vbCrLf
strCmd = strCmd & "BEGIN" & vbCrLf
strCmd = strCmd & "IF (EXISTS (SELECT *" & vbCrLf
strCmd = strCmd & " From msdb.dbo.sysjobservers" & vbCrLf
strCmd = strCmd & " WHERE (job_id = @JobID) AND (server_id <> 0)))" & vbCrLf
strCmd = strCmd & "BEGIN" & vbCrLf
strCmd = strCmd & "RAISERROR (N'无法导入作业“OraToSQL_Job”，因为已经有相同名称的多重服务器作业。', 16, 1)" & vbCrLf
strCmd = strCmd & "GoTo QuitWithRollback" & vbCrLf
strCmd = strCmd & "End" & vbCrLf
strCmd = strCmd & "Else" & vbCrLf
strCmd = strCmd & "EXECUTE msdb.dbo.sp_delete_job @job_name = N'OraToSQL_Job'" & vbCrLf
strCmd = strCmd & "SELECT @JobID = NULL" & vbCrLf
strCmd = strCmd & "End" & vbCrLf
strCmd = strCmd & "BEGIN" & vbCrLf
strCmd = strCmd & "EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'OraToSQL_Job', @owner_login_name = N'sa', @description = N'Nothing', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 3, @delete_level= 0" & vbCrLf
strCmd = strCmd & "IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback" & vbCrLf
strCmd = strCmd & "EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'CopyToSQL', @command = N'DTSRun /s " & Text4.Text & " /u sa /p " & Text3(1).Text & _
" /n OraToSql', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 2, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'C:\DTS.Log', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2" & vbCrL
strCmd = strCmd & "IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback" & vbCrLf
strCmd = strCmd & "EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1" & vbCrLf
strCmd = strCmd & "IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback" & vbCrLf
strCmd = strCmd & " EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'EverDay Do', @enabled = 1, @freq_type = 4, @active_start_date = 20030305, @active_start_time = 63000, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 6, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959" & vbCrLf
strCmd = strCmd & "IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback" & vbCrLf
strCmd = strCmd & "EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'" & vbCrLf
strCmd = strCmd & "IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback" & vbCrLf
strCmd = strCmd & "End" & vbCrLf
strCmd = strCmd & "COMMIT TRANSACTION" & vbCrLf
strCmd = strCmd & "GoTo EndSave" & vbCrLf
strCmd = strCmd & "QuitWithRollback:" & vbCrLf
strCmd = strCmd & "IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION" & vbCrLf
strCmd = strCmd & "EndSave:" & vbCrLf
Dim oSqlCon As ADODB.Connection
Dim oSqlCmd As New ADODB.Command
Set oSqlCon = New ADODB.Connection
With oSqlCon
.Provider = "SQLOLEDB"
.Properties("User ID") = Text2(1).Text
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -