Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
Set goPackage = goPackageOld goPackage.Name = "新建包"
goPackage.Description = "DTS 包描述"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
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
Dim oConnProperty As DTS.OleDBProperty'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------Dim oConnection As DTS.Connection2'------------- a new connection defined below.
'For security purposes, the password is never scriptedSet oConnection = goPackage.Connections.New("SQLOLEDB") oConnection.ConnectionProperties("Integrated Security") = "SSPI"
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("Initial Catalog") = "HospitalDB"
oConnection.ConnectionProperties("Data Source") = "YTD1508"
oConnection.ConnectionProperties("Application Name") = "DTS 导入/导出向导"
oConnection.Name = "连接1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "YTD1508"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "HospitalDB"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"goPackage.Connections.Add oConnection
Set oConnection = Nothing'------------- a new connection defined below.
'For security purposes, the password is never scriptedSet oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0") oConnection.ConnectionProperties("Data Source") = "C:\Documents and Settings\Administrator\桌面\新建文件夹"
oConnection.ConnectionProperties("Extended Properties") = "dBase 5.0"
oConnection.Name = "连接2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\Documents and Settings\Administrator\桌面\新建文件夹"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"goPackage.Connections.Add oConnection
Set oConnection = Nothing'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint'------------- a new step defined belowSet oStep = goPackage.Steps.New oStep.Name = "创建表 结果 步骤"
oStep.Description = "创建表 结果 步骤"
oStep.ExecutionStatus = 1
oStep.TaskName = "创建表 结果 任务"
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
Set oStep = Nothing'------------- a new step defined belowSet oStep = goPackage.Steps.New oStep.Name = "Copy Data from 结果 to 结果 步骤"
oStep.Description = "Copy Data from 结果 to 结果 步骤"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from 结果 to 结果 任务"
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
Set oStep = Nothing'------------- a precedence constraint for steps defined belowSet oStep = goPackage.Steps("Copy Data from 结果 to 结果 步骤")
Set oPrecConstraint = oStep.precedenceConstraints.New("创建表 结果 步骤")
oPrecConstraint.StepName = "创建表 结果 步骤"
oPrecConstraint.PrecedenceBasis = 0
oPrecConstraint.Value = 4
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------'------------- call Task_Sub1 for task 创建表 结果 任务 (创建表 结果 任务)
Call Task_Sub1(goPackage)'------------- call Task_Sub2 for task Copy Data from 结果 to 结果 任务 (Copy Data from 结果 to 结果 任务)
Call Task_Sub2(goPackage)'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
Set goPackage = NothingSet goPackageOld = NothingEnd Sub
'------------- define Task_Sub1 for task 创建表 结果 任务 (创建表 结果 任务)
Public Sub Task_Sub1(ByVal goPackage As Object)Dim oTask As DTS.Task
Dim oLookup As DTS.LookupDim oCustomTask1 As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask1 = oTask.CustomTask oCustomTask1.Name = "创建表 结果 任务"
oCustomTask1.Description = "创建表 结果 任务"
oCustomTask1.SQLStatement = "CREATE TABLE `结果` (" & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`int` Long , " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`TinyInt` Byte , " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`SmallInt` Short , " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`Real` Single , " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`Float` Double " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & ")"
oCustomTask1.ConnectionID = 2
oCustomTask1.CommandTimeout = 0
oCustomTask1.OutputAsRecordset = False
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = NothingEnd Sub
Public Sub Task_Sub2(ByVal goPackage As Object)Dim oTask As DTS.Task
Dim oLookup As DTS.LookupDim oCustomTask2 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask2 = oTask.CustomTask oCustomTask2.Name = "Copy Data from 结果 to 结果 任务"
oCustomTask2.Description = "Copy Data from 结果 to 结果 任务"
oCustomTask2.SourceConnectionID = 1
oCustomTask2.SourceSQLStatement = "select [Test].[int], [Test].[TinyInt], [Test].[SmallInt], [Test].[Real], [Test].[Float]" & vbCrLf
oCustomTask2.SourceSQLStatement = oCustomTask2.SourceSQLStatement & "from [Test]" & vbCrLf
oCustomTask2.SourceSQLStatement = oCustomTask2.SourceSQLStatement & "order by [Test].[int], [Test].[TinyInt], [Test].[SmallInt], [Test].[Real], [Test].[Float]"
oCustomTask2.DestinationConnectionID = 2
oCustomTask2.DestinationObjectName = "结果"
oCustomTask2.ProgressRowCount = 1000
oCustomTask2.MaximumErrorCount = 0
oCustomTask2.FetchBufferSize = 1
oCustomTask2.UseFastLoad = True
oCustomTask2.InsertCommitSize = 0
oCustomTask2.ExceptionFileColumnDelimiter = "|"
oCustomTask2.ExceptionFileRowDelimiter = vbCrLf
oCustomTask2.AllowIdentityInserts = False
oCustomTask2.FirstRow = 0
oCustomTask2.LastRow = 0
oCustomTask2.FastLoadOptions = 2
oCustomTask2.ExceptionFileOptions = 1
oCustomTask2.DataPumpOptions = 0
Call oCustomTask2_Trans_Sub1(oCustomTask2)
goPackage.Tasks.Add oTask
Set oCustomTask2 = Nothing
Set oTask = NothingEnd Sub
Public Sub oCustomTask2_Trans_Sub1(ByVal oCustomTask2 As Object) Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask2.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
Set oColumn = oTransformation.SourceColumns.New("int", 1)
oColumn.Name = "int"
oColumn.Ordinal = 1
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing Set oColumn = oTransformation.SourceColumns.New("TinyInt", 2)
oColumn.Name = "TinyInt"
oColumn.Ordinal = 2
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 17
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing Set oColumn = oTransformation.SourceColumns.New("SmallInt", 3)
oColumn.Name = "SmallInt"
oColumn.Ordinal = 3
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 2
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing Set oColumn = oTransformation.SourceColumns.New("Real", 4)
oColumn.Name = "Real"
oColumn.Ordinal = 4
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 4
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing Set oColumn = oTransformation.SourceColumns.New("Float", 5)
oColumn.Name = "Float"
oColumn.Ordinal = 5
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing Set oColumn = oTransformation.DestinationColumns.New("int", 1)
oColumn.Name = "int"
oColumn.Ordinal = 1
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing Set oColumn = oTransformation.DestinationColumns.New("TinyInt", 2)
oColumn.Name = "TinyInt"
oColumn.Ordinal = 2
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 17
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing Set oColumn = oTransformation.DestinationColumns.New("SmallInt", 3)
oColumn.Name = "SmallInt"
oColumn.Ordinal = 3
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 2
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing Set oColumn = oTransformation.DestinationColumns.New("Real", 4)
oColumn.Name = "Real"
oColumn.Ordinal = 4
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 4
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing Set oColumn = oTransformation.DestinationColumns.New("Float", 5)
oColumn.Name = "Float"
oColumn.Ordinal = 5
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing Set oTransProps = oTransformation.TransformServerProperties
Set oTransProps = Nothing oCustomTask2.Transformations.Add oTransformation
Set oTransformation = NothingEnd Sub