DTS 导出
2.24/5 (15投票s)
关于从一个服务器导出 MS DTS 包到另一个服务器的文章。
执行摘要
本文面向熟悉 Microsoft DTS 包的软件开发人员。它提供了 DTS 包的基本概述以及导出 DTS 包的需求，并附带了一个示例场景。通过这个示例工具，开发人员可以将 DTS 包从一个 SQL Server 导出到另一个 SQL Server，从而节省时间和提高生产力。
引言
在当今的商业世界中，一个组织由许多不同大小的应用程序组成。即使业务包含许多应用程序，数据也必须在一个集中的区域进行访问。也就是说，组织需要在应用程序或服务器之间集中数据。不同的供应商有不同的工具/应用程序来在这些服务器之间传输数据。Microsoft 的 MSSQL Server 使用 MS 数据转换服务 (DTS) 来实现此功能。
DTS（数据转换服务）是一套工具，用于从离散源提取、转换和整合数据到单个/多个目标。DTS 使用一个名为 DTS 包的对象模型来传输数据。DTS 包是任务、步骤、连接等对象的集合，这些对象可以作为对象存储在 SQL Server 中。尽管包存储在服务器中，但在导出场景中[参见第 2 节中提到的示例场景]，它与表、视图等其他对象略有不同。也就是说，DTS 包不能直接从一个 SQL Server 导出到另一个 SQL Server。本文提供了关于将这些包从一个 SQL Server 导出到另一个 SQL Server 的不同方法的信息。
导出 DTS 包的需求
在软件行业中，从软件开发到软件实施，其周期包含许多层，例如一套用于开发，另一套用于测试等等。
（即，如开发服务器、测试服务器和生产服务器）。开发环境中 DTS 包中指向的数据可能与测试环境中的不同，并且在生产环境中也会有所不同。
以下示例场景对此进行了简要说明。
示例场景
假设一个应用程序是为 10 个国家设计的，每个国家根据业务逻辑有 6 个包。因此，该应用程序总共包含 60 个（10 X 6 = 60）包。假设每个包从数据服务器 X、数据服务器 Y 向一个集中式服务器传输数据。
在开发阶段，这些包在 DEVELOPMENT_SERVER 中开发，数据源将指向开发环境。在测试阶段，这些包需要移动到 TESTING_SERVER，并且数据源应指向各自的服务器。在实施时，这些包需要移动到 PRODUCTION_SERVER。
图 1. 导出 DTS 包的需求
因此，设计的 DTS 包需要移动到测试环境和生产环境。如前所述，这些包不能像其他 SQL Server 对象（如表、视图、存储过程等）一样移动。这些包的移动将在本文的后续部分中使用相同的示例场景进行解释。
将这些包从一个服务器移动到另一个服务器的常见方法是，打开每个包并使用“另存为”选项，然后将包保存到目标服务器。但是根据示例场景，应用程序包含大约 60 个包，根据我们的示例应用程序，这些包最初需要移动到 TESTING_SERVER 服务器，然后是 PRODUCTION_SERVER 服务器。因此，实际上需要导出 120 个（60 X 2 = 120）包。
逐个打开包并“另存为”到目标服务器是一种繁琐且耗时的方法。但是如果应用程序包含的 DTS 包很少，这种方法是最好的。因此，作为一种更好的方法来将所有包从一个服务器导出到另一个服务器，可以考虑使用带有SYSDTSPACKAGES的第二种 DTS 包方法。
带有 SYSDTSPACKAGES 对象的 DTS 包
SQL Server 将所有 DTS 包存储到 msdb.dbo.sysdtspackages 对象中。将此对象从一个服务器导出到另一个服务器，就像将包从一个服务器导出到另一个服务器一样简单。导出这些包或 msdb.dbo.sysdtspackages 表可以通过一个简单的 DTS 包完成，该包将源服务器的 msdb.dbo.sysdtspackages 传输到目标服务器的 msdb.dbo.sysdtspackages。这种方法非常有效，可以快速高效地将所有包从一个服务器传输到另一个服务器。
本文的下一节将提供用于以编程方式构建 DTS 包的代码，该包包含一个任务和步骤对象来导出这些包。完整功能已设计为带有此工具，可随本文下载，开发人员可以根据业务需求修改此工具。
（注意：此工具使用 Visual Basic 6.0 开发，包含的类型库有 Microsoft ADO 2.5、Microsoft DTS Package Object Library、
Microsoft DTS 自定义任务对象库和
Microsoft DTS Data PumpScripting 对象库）
'***********************************************************************
'Populate DTS Packages
'This subroutine is used to fetch the latest version of DTS packages from
'the SQL Server
'***********************************************************************
Public Sub populateSysDTS()
Dim SQL As String
Dim ObjCon As New ADODB.Connection
Dim ObjRs As New ADODB.Recordset
On Error GoTo ErrHandler:
ObjCon.Open "Provider=SQLOLEDB;SERVER=" & gsSrcServerName & ";UID=" &_
gsSrcUserId & ";PWD=" & gsSrcPwd
SQL = "SELECT A.name FROM msdb.dbo.sysdtspackages AS A INNER JOIN " &_
"(SELECT B.[name] , B.[id], B.[createdate] " & _
" FROM msdb.dbo.sysdtspackages B " & _
" GROUP BY B.[name], B.[id], B.[createdate] HAVING B.[createdate]" &_
" IN (SELECT TOP 1 C.[createdate]" & _
" FROM msdb.dbo.sysdtspackages C Where B.[ID] = C.[ID] " & _
" ORDER BY C.[createdate] DESC) ) AS B ON A.[id] = B.[id] " &_
"AND A.[createdate] = B.[createdate]"
ObjRs.Open SQL, ObjCon, 1, 3
Erase sPackageNames
ReDim Preserve sPackageNames(0)
Do Until ObjRs.EOF
sPackageNames(UBound(sPackageNames)) = ObjRs(0)
ReDim Preserve sPackageNames(UBound(sPackageNames) + 1)
ObjRs.MoveNext
Loop
ObjRs.Close
ObjCon.Close
Set ObjRs = Nothing
Set ObjCon = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Number & vbCrLf & Err.Description, vbInformation, "DTS Export"
End
End Sub
'***********************************************************************
'Create DTS Task
'This subroutine is used to create a DTS task object.
'***********************************************************************
Public Sub CreateTask(ByVal ObjCustomTask As Object)
Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = _
ObjCustomTask.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DTS_Export DataPump"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
ObjCustomTask.Transformations.Add oTransformation
Set oTransformation = Nothing
End Sub
'***********************************************************************
'Create DTS Connection
'This function will create a DTS Connection Object and will return the same.
'***********************************************************************
Public Function MakeConnection
(
ByRef lsStrConnectionName As String,
lsStrAppName As String,
ByRef lsConnectionId As Integer,
ByRef lsStrDataSource As String,
ByRef lsStrDatabase As String,
ByRef lsStrUserId As String,
ByRef lsStrPassword As String
) As Connection
Set ObjConnection = objPackage.Connections.New("SQLOLEDB")
ObjConnection.Name = lsStrConnectionName
ObjConnection.ConnectionProperties("Persist Security Info") = True
ObjConnection.ConnectionProperties("Data Source") = lsStrDataSource
ObjConnection.ConnectionProperties("Initial Catalog") = lsStrDatabase
ObjConnection.ConnectionProperties("User ID") = lsStrUserId
ObjConnection.Password = lsStrPassword
ObjConnection.ConnectionProperties("Application Name") = lsStrAppName
ObjConnection.ID = lsConnectionId
ObjConnection.ConnectionTimeout = 60
ObjConnection.UseTrustedConnection = False
ObjConnection.UseDSL = False
Set MakeConnection = ObjConnection
Set ObjConnection = Nothing
End Function
'***********************************************************************
'Create DTS Step
'This function will create a DTS Step Object and will return the same.
'***********************************************************************
Public Function CreateStep(ByVal loObjPack As Object) As Step
Dim ObjStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint
Set ObjStep = loObjPack.Steps.New
ObjStep.Name = "DTS_Export_Step"
ObjStep.Description = "DTS-Export Step"
ObjStep.ExecutionStatus = 1
ObjStep.TaskName = "DTS_Export_DataPump"
ObjStep.CommitSuccess = False
ObjStep.RollbackFailure = False
ObjStep.CloseConnection = False
ObjStep.IsPackageDSORowset = False
ObjStep.JoinTransactionIfPresent = False
ObjStep.DisableStep = False
ObjStep.FailPackageOnError = False
Set CreateStep = ObjStep
End Function
'***********************************************************************
'Sub routine used to export DTS Objects
'***********************************************************************
Private Sub exportPackage()
Dim lsPackages As String
Dim clsServer As New DTS_Export
Dim ObjTask As DTS.Task
Dim ObjCustomTask As DTS.DataPumpTask2
Dim lsSourceSQLStatement As String
lsPackages = ""
If lstTgtServer.ListCount > 0 Then
If MsgBox("Press OK to Export this packages", _
vbInformation+ vbOKCancel, _
"DTS-Export") = vbOK Then
ctrlPB.Max = lstTgtServer.ListCount + 10
ctrlPB.Visible = True
Screen.MousePointer = vbHourglass
For iindex = 0 To lstTgtServer.ListCount - 1
ctrlPB.Value = iindex + 1
If lsPackages = "" Then
lsPackages = "'" & lstTgtServer.List(iindex) & "'"
Else
lsPackages = lsPackages & ",'" & lstTgtServer.List(iindex) & "'"
End If
Next
objPackage.Name = "DTS Export Package"
objPackage.WriteCompletionStatusToNTEventLog = False
objPackage.FailOnError = False
objPackage.PackagePriorityClass = 2
objPackage.MaxConcurrentSteps = 4
objPackage.LineageOptions = 0
objPackage.UseTransaction = True
objPackage.TransactionIsolationLevel = 4096
objPackage.AutoCommitTransaction = True
objPackage.RepositoryMetadataOptions = 0
objPackage.UseOLEDBServiceComponents = True
objPackage.LogToSQLServer = False
objPackage.LogServerFlags = 0
objPackage.FailPackageOnLogFailure = False
objPackage.ExplicitGlobalVariables = False
objPackage.PackageType = 0
Set ObjConnection_1 =_
clsServer.MakeConnection("DTS-Export Source Server", "DTS_Exprt",1,
gsSrcServerName, "msdb", gsSrcUserId, gsSrcPwd)
objPackage.Connections.Add ObjConnection_1
Set ObjConnection_1 = _
clsServer.MakeConnection("DTS-Export Target Server", "DTS_Exprt",2,
gsTgtServerName, "msdb", gsTgtUserId, gsTgtPwd)
objPackage.Connections.Add ObjConnection_1
Set ObjTask = objPackage.Tasks.New("DTSDataPumpTask")
Set ObjCustomTask = ObjTask.CustomTask
ObjCustomTask.Name = "DTS_Export_DataPump"
ObjCustomTask.Description = "Tranfer packages"
ObjCustomTask.SourceConnectionID = 1
lsSourceSQLStatement = " SELECT a.name,a.id,a.versionid," &_
"a.description,a.categoryid, " &_
"a.createdate,a.owner,a.packagedata,a.owner_sid FROM" &_
"msdb.dbo.sysdtspackages AS A " &_
"INNER JOIN (SELECT B.[name] , B.[id], B.[createdate]" & _
" FROM msdb.dbo.sysdtspackages B lsSourceSQLStatement = " &_
"lsSourceSQLStatement" &_
" WHERE NAME IN(" & lsPackages & ")" & _
" GROUP BY B.[name], B.[id], B.[createdate]" &_
"HAVING B.[createdate] IN (SELECT TOP 1 C.[createdate]" & _
" FROM msdb.dbo.sysdtspackages C Where B.[ID] = C.[ID]" & _
" ORDER BY C.[createdate] DESC) ) AS B ON A.[id] = " &_
"B.[id] AND A.[createdate] = B.[createdate]"
ObjCustomTask.SourceSQLStatement = lsSourceSQLStatement
ObjCustomTask.DestinationConnectionID = 2
ObjCustomTask.DestinationObjectName = "msdb.dbo.sysdtspackages"
ObjCustomTask.ProgressRowCount = 1000
ObjCustomTask.MaximumErrorCount = 0
ObjCustomTask.FetchBufferSize = 1
ObjCustomTask.UseFastLoad = True
ObjCustomTask.InsertCommitSize = 0
ObjCustomTask.AllowIdentityInserts = False
ObjCustomTask.FirstRow = "0"
ObjCustomTask.LastRow = "0"
ObjCustomTask.FastLoadOptions = 2
ObjCustomTask.ExceptionFileOptions = 1
ObjCustomTask.DataPumpOptions = 0
Set ObjStep_1 = clsServer.CreateStep(objPackage)
objPackage.Steps.Add ObjStep_1
clsServer.CreateTask ObjCustomTask
objPackage.Tasks.Add ObjTask
objPackage.Execute
'objPackage.SaveToSQLServer "TARGET_SERVER", "sa", "", DTSSQLStgFlag_Default
objPackage.UnInitialize
ctrlPB.Value = iindex + 10
lstTgtServer.Clear
Screen.MousePointer = vbDefault
End If
Else
MsgBox "To Export DTS packages, at least one package must be selected.",_
vbInformation, "DTS Export"
End If
End Sub
上述代码将调用每个包对象并更新连接属性。调用每个包的设计有点糟糕，但即使如此也可以在单独的线程中运行。一旦此代码的执行在不同的线程中运行，开发人员就可以开始处理其他任务。否则，开发人员必须打开每个包来编辑属性。这又将是一个繁琐的过程，会浪费大量时间。因此，开发人员可以使用 DTSPackage 对象模型来节省时间并提高生产力。通过将此过程自动化，可以节省开发人员的时间并提高生产力。
结论
本文提供了 DTS 包的基本概述，以及从一个 SQL Server 到另一个 SQL Server 导出 DTS 包的需求，并附带了一个示例场景。本文还提供了一个导出 DTS 包的工具，从而提高了生产力。尽管此工具专门用于导出 DTS 包，但建议仅在一次性导出大量 DTS 包时使用。开发人员可以根据自己的需求修改此工具的代码，以提高生产力。
参考文献
DTS 包对象模型
如上所述，DTS 包已快速导出。现在开发人员可以在目标服务器中看到这些包。但是使用这个 sysdtspackage 对象模型不会导出任何与包相关的信息，例如连接属性、用户 ID 等，
根据示例场景，即使所有这些包都已导出，它们仍然会指向旧的连接属性。（如果已从测试环境导出到生产环境，则连接仍将指向测试服务器。）要更改 DTS 包的属性，Microsoft 提供了 DTS 包对象模型，开发人员可以在其中访问单个 DTS 包对象并以编程方式修改设置。以下代码用于更新连接设置。
'**********************************************************************
'Section used to update the DTS Connection settings
'***********************************************************************
objPackage.LoadFromSQLServer gsSrcServerName, gsSrcUserId, gsSrcPwd,_
DTSSQLStgFlag_Default, , , , lsPackageName
Set cns = objPackage.Connections
For Each cn In cns
If UCase(cn.DataSource) = UCase(gsUpdateSRCServer) And _
UCase(cn.UserID) = UCase(gsUpdateSRCUserId) Then 'And cn.Password = gsUpdateSRCPwd Then
cn.DataSource = gsUpdateTGTServer
cn.UserID = gsUpdateTGTUserId
cn.Password = gsUpdateTGTPwd
objPackage.SaveToSQLServer gsSrcServerName, gsSrcUserId, gsSrcPwd, DTSSQLStgFlag_Default
End If
Next
Set objPackage = Nothing
上述代码将调用每个包对象并更新连接属性。调用每个包的设计有点糟糕，但即使如此也可以在单独的线程中运行。一旦此代码的执行在不同的线程中运行，开发人员就可以开始处理其他任务。否则，开发人员必须打开每个包来编辑属性。这又将是一个繁琐的过程，会浪费大量时间。因此，开发人员可以使用 DTSPackage 对象模型来节省时间并提高生产力。通过将此过程自动化，可以节省开发人员的时间并提高生产力。
结论
本文提供了 DTS 包的基本概述，以及从一个 SQL Server 到另一个 SQL Server 导出 DTS 包的需求，并附带了一个示例场景。本文还提供了一个导出 DTS 包的工具，从而提高了生产力。尽管此工具专门用于导出 DTS 包，但建议仅在一次性导出大量 DTS 包时使用。开发人员可以根据自己的需求修改此工具的代码，以提高生产力。