通过 VB.NET 执行 DTS

Suresh Suthar

4.86/5 (11投票s)

2009 年 9 月 11 日

CPOL

10分钟阅读

83111

990

如何通过 VB.NET 执行 DTS 包并使用全局变量。

下载源代码 - 120 KB

背景

前几天，我被要求将一些文本文件导入我们的数据库（MS SQL Server 2000，没错！我们还在使用它）。我使用了 DTS 来导入这些文件。然后我意识到，作为一名程序员或数据库开发人员，您可以轻松地将这些类型的文件导入数据库，但我的同事却无法做到。所以我想，为什么不创建一个应用程序，让他只需选择文件并单击上传按钮即可导入文件。我在网上搜索了有关在 VB.NET 中使用 DTS 的信息，在 CP 上找到了很多内容，但在 CP 上很少。我开发了一个运行良好的应用程序，现在我将我的代码发布到 CP 上，以帮助初学者开发使用 DTS 的前端应用程序。

(所有引用的网站都列在下面。非常感谢他们。)

注意：由于英语是我的第二语言，请原谅我在拼写和语法方面的不足。

引言

在 CP 上搜索时，我发现了一篇关于 DTS (VB.NET) 的优秀文章。它简洁明了地解释了在 DTS 中使用全局变量。我将简要介绍 DTS。（我从下面引用的其他网站复制了一些内容。有关详细信息，请参阅这些链接。）

什么是 DTS？

SQL Server 2000 中的数据转换服务 (DTS) 提供了一组图形化工具和可编程对象，以帮助管理员和开发人员解决数据移动问题，包括从不同来源提取、转换和整合数据到单个或多个目标。Microsoft SQL Server 2000 中的数据转换服务是一个高性能数据泵。它是一种用于复制、移动、整合、清理和验证数据的工具。数据泵从数据源加载每一行，修改该行中的值，并将该行插入数据目标。DTS 也是一种面向数据的编程的快速应用程序开发工具。它提供了一套全面的数据操作工具，这些工具组织在一个方便且功能强大的开发环境中。

什么是 DTS 包？

DTS 包是一组相关的对象（连接、任务和工作流），可用于访问、转换和操作各种来源（包括文本文件和关系数据库）的数据。DTS 随 SQL Server 7.0 一起发布，并因其巨大的受欢迎程度而被引入 SQL Server 2000（在 SQL Server 2005 中作为 SISS 包）。

如何创建 DTS 包？

要创建 DTS 包，我们使用 DTS 设计器（可以通过企业管理器访问）。我们稍后将详细讨论 DTS 设计器，但目前，我们只需要知道它可以创建两种类型的对象：连接和任务。连接对象表示到数据存储的连接，例如 SQL Server 2000、Oracle 数据库，甚至是一个文本文件。它们用于使任务能够访问它们需要转换或操作的数据。任务对象允许我们使用通过连接对象访问的数据。任务允许我们复制、查询或操作数据，并且通常负责在 DTS 包中提供任何类型的活动。我们可以将 DTS 包存储在服务器上，也可以存储在本地驱动器上的结构化存储文件中，然后在我们的应用程序中使用此包。

(有关更多详细信息，请访问引用的链接。) 本文的目的是解释如何使用 VB.NET 执行 DTS 包。

使用 VB.NET 执行 DTS 包

有几种方法可以在 VB.NET 中执行 DTS 包。要通过 VB.NET 使用 DTS 包，我们必须安装以下组件

我们需要在要开发包的计算机上安装 Microsoft SQL Server 客户端工具和 Visual Studio 2005。
我们需要在要运行包的计算机上安装 SQL Server 客户端工具和 .NET Framework 2.0。

从“添加引用”对话框的“COM 组件”选项卡中，我们必须选择下表中列出的引用

参考	DTS 功能	库文件
Microsoft DTSPackage 对象库	任何 DTS 对象或功能	dtspkg.dll
Microsoft DTSDataPump 脚本对象库	SQL Server 提供的任何转换或任何 DTS 脚本对象	dtspump.dll
Microsoft DTS 自定义任务对象库	消息队列任务、文件传输协议任务或动态属性任务	custtask.dll

添加引用

使用 VB.NET 检索所有可用 DTS 包的列表

有许多方法可以实现此要求。您可以使用 SQL-DMO 来完成，也可以选择使用一些数据库查询的传统方法。在此示例中，我将使用一个内置的存储过程来完成此操作。

''To Retrieve all the available DTS package
Try
    Dim Cn As New SqlConnection
    Cn.ConnectionString = Get_Connection_String()
    Dim Da As New SqlDataAdapter("exec sp_enum_dtspackages", Cn)
    Dim Dt As New DataTable
    Da.Fill(Dt)
    dgDTS.DataSource = Dt
Catch exSQL As SqlException
    MsgBox("Error: " & exSQL.Message, MsgBoxStyle.Critical)
Catch ex As Exception
    MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical)
End Try

执行存储在服务器上的 DTS

现在，我们将看到如何执行存储在服务器上的 DTS 包。这非常简单。我们必须导入 DTS 命名空间（在为项目添加引用后），并创建 `DTS.Package` 类的实例。现在，我们将使用 `Package` 类的 `LoadFromSQLServer` 方法来执行服务器上的 DTS。

''Execute DTS Stored on Server
Dim dtsp As New DTS.Package
dtsp.LoadFromSQLServer(ServerName:="ServerName", ServerUserName:="UserName", _
   ServerPassword:="Password", PackageName:="YourDTSPackageName")
dtsp.Execute()
MessageBox.Show("Action completed")
Me.btnUpload.Enabled = False

执行存储在本地驱动器上的结构化文件 DTS

我们可以将 DTS 包存储为本地驱动器上的结构化存储文件，以便在我们的应用程序中进一步使用。将 DTS 保存为结构化存储文件后，我们就可以在 VB.NET 代码中使用了，如下所示：

Private Sub RunPackage()
    'Run the package stored in file 
    Dim objPackage As DTS.Package2
    Dim objStep As DTS.Step
    Dim objTask As DTS.Task
    Dim objExecobjPkg As DTS.ExecutePackageTask

    objPackage = New DTS.Package
    objPackage.FailOnError = True

    'Create the step and task. Specify the package to be run, 
    'and link the step to the task.
    objStep = objPackage.Steps.New
    objTask = objPackage.Tasks.New("DTSExecutePackageTask")
    objExecobjPkg = objTask.CustomTask

    With objExecobjPkg
        ''.PackagePassword = "user"
        .FileName = My.Application.Info.DirectoryPath & _
                    "\DTSPakages\Import_Stores_DTS.dts"
        .Name = "ExecobjPkgTask"
    End With

    With objStep
        .TaskName = objExecobjPkg.Name
        .Name = "ExecobjPkgStep"
        .ExecuteInMainThread = True
    End With
    objPackage.Steps.Add(objStep)
    objPackage.Tasks.Add(objTask)

    'Run the package and release references.
    objPackage.Execute()

    ''Dim d As Double = objStep.ExecutionTime

    objExecobjPkg = Nothing
    objTask = Nothing
    objStep = Nothing

    objPackage.UnInitialize()
End Sub

在 DTS 中使用全局变量

假设我们要导入 Excel 文件，并且想将 Excel 文件名动态传递给 DTS。我们可以通过向 DTS 包添加全局变量来实现这一点。此处说明了如何添加全局变量：DTS (VB.NET)。

我也会尝试在下面的图片中解释这一点

首先，我们将使用导入和导出向导将 Excel 文件导入我们的数据库，并将此包保存到 SQL Server。然后，我们将打开 DTS 包在设计器窗口中。它看起来会像这样

然后，我们将从任务窗格中添加一个动态属性任务，然后单击“添加”按钮为我们的 DTS 包添加一个动态属性。

之后，将显示一个对话框。在这里，我们将选择我们的 Excel 文件连接对象，然后在 `DataSource` 属性中，我们将双击。

在这里，我们单击“创建全局变量”按钮。

将出现一个新窗口。在此窗口中，我们单击“新建”按钮并添加一个类型为 String 的变量，名为 `FileName`。然后，我们将单击“确定”按钮。

在此窗口中，我们将选择变量 `FileName`，然后单击“确定”按钮。现在，我们已创建了一个名为 `FileName` 的全局变量，并将其分配给 `Connection1` 的动态属性。

现在，我们将选择 `connection1` 对象和动态属性任务对象，然后转到“工作流”并选择“成功”。就这样，我们的 DTS 包就准备好了。最后，我们将它保存为结构化存储文件。为此，请转到包并单击对话框中的*另存为*。在*另存为*选项中选择结构化存储文件，然后单击“保存”。我们已经生成了结构化存储 DTS 包。现在，我们将通过 VB.NET 代码执行此 DTS，并将使用 `GlobalVariables` 的 `Remove` 和 `AddGlobalVariable` 方法访问 `FileName` 全局变量。代码示例如下

Private Sub ExecuteDTS(ByVal FILENAME As String)
    Dim objPkg As New DTS.Package2

    Try
        Dim dtsPath As String = My.Application.Info.DirectoryPath & _
                                "\DTSPakages\Import_Stores_DTS.dts"
        'Load Structured Storage File (the DTS)
        objPkg.LoadFromStorageFile(dtsPath, "")
        objPkg.FailOnError = True

        'Remove all Global Variables
        For Each gv As GlobalVariable In objPkg.GlobalVariables
            objPkg.GlobalVariables.Remove(gv.Name)
        Next

        'Set the FileName Global Variable
        objPkg.GlobalVariables.AddGlobalVariable("FileName", FILENAME)

        objPkg.Execute()
        'Release the object
        objPkg = Nothing
        'UnInitialize the object
        objPkg.UnInitialize()

        MsgBox("Data Imported Successfully.", MsgBoxStyle.Information)
    Catch exc As System.Runtime.InteropServices.COMException
        MessageBox.Show(exc.Message, "Error!!!", _
                        MessageBoxButtons.OK, MessageBoxIcon.Error)
    Catch exc As Exception
        MessageBox.Show(exc.Message, "Error!!!", _
                        MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
End Sub

在代码中创建 DTS（无需在服务器上创建 DTS），在运行时

此时，我们已准备好开始编码。以下 VB.NET 代码在 Pubs 数据库的 Authors 表中执行批量插入。代码利用了以下 DTS 对象

包
Connection
步骤
任务
BulkInsertTask

Package 将所有内容组合在一起，而 Step 是 DTS 过程中的一个运行。这些运行包括单个任务。请注意，在列表 A 中，`Package` 和 `Step` 对象都声明为在类名后附加了*2*。有两个版本的类，第一个版本（无数字）与早期 SQL Server 版本一起使用。由于您使用的是 SQL Server 2000，因此可以利用新版本。

在代码中，为 `Connection` 对象分配了连接到服务器的必要属性（例如，用户名、密码、数据源等）。建立连接后，将其添加到包中。接下来，创建 `Step` 对象以及 `Task` 对象。最后，将对象添加到包中，并通过 `Execute` 方法处理。

基本上，代码将文本文件中的数据导入 Pubs 数据库。文本文件的格式如下

666-66-6|Tester|Chester|214 243-6666|1 Main Street.|Somewhere|PA|15003|1

代码包含在 Try/Catch/Finally 块中，以处理任何意外错误。对象在 Finally 块中正确处理。

代码很简单，并且在大多数组织中，执行此类插入是一项常见任务。该代码可以轻松地在 Windows 服务中创建并安排在夜间执行插入。

'>>Using DTS package withoug creating on server  
Private Sub ExecuteDTS(ByVal FileName As String)
    Dim objPackage As New DTS.Package2()
    Dim objConnection As DTS.Connection
    Dim objStep As DTS.Step2
    Dim objTask As DTS.Task
    Dim objCustomTask As DTS.BulkInsertTask
    Try
        'initialize connection object
    objConnection = objPackage.Connections.New("SQLOLEDB")
    objStep = objPackage.Steps.New
    objTask = objPackage.Tasks.New("DTSBulkInsertTask")
    objCustomTask = objTask.CustomTask
    With objConnection
          objConnection.Catalog = "pubs"
      objConnection.DataSource = "(local)"
          objConnection.ID = 1
      objConnection.UseTrustedConnection = True
          objConnection.UserID = "sa"
      objConnection.Password = "123"
    End With
    objPackage.Connections.Add(objConnection)
    objConnection = Nothing
    With objStep
          .Name = "PkgStep"
      .ExecuteInMainThread = True
    End With
    'initiallize custome task object
    With objCustomTask
          .Name = "PkgTask"
      .DataFile = FileName
          .ConnectionID = 1
      .DestinationTableName = "pubs..stores"
          .FieldTerminator = "|"
      .RowTerminator = "\r\n"
    End With
    objStep.TaskName = objCustomTask.Name
    With objPackage
          .Steps.Add(objStep)
      .Tasks.Add(objTask)
      .FailOnError = True
    End With
    'execute dts package
    objPackage.Execute()
    MsgBox("Data Imported Successfully.", MsgBoxStyle.Information)
    Catch ex As Exception
    MsgBox("Error: " & vbCrLf & ex.Message, vbExclamation, objPackage.Name)
    Finally
    'release objects
    objConnection = Nothing
    objCustomTask = Nothing
    objTask = Nothing
    objStep = Nothing
    If Not (objPackage Is Nothing) Then
          objPackage.UnInitialize()
    End If
    End Try
End Sub

使用 DTS 复制数据库

下面提供的代码展示了 DTS 对象公开的另一项功能。它自动化了将数据库架构和数据从一个数据库复制到另一个数据库的过程。请注意：在运行例程之前应创建目标数据库。该数据库使用与列表 A 相同的对象，但使用了 `TransferObjectsTask2` 对象来执行实际的数据库复制。该类的使用方法是设置目标和源数据库以及其他选项。

Private Sub CopyDatabase()
    Dim oPackage As New DTS.Package2()
    Dim oStep As DTS.Step2
    Dim oTask As DTS.Task
    Dim oCustomTask As DTS.TransferObjectsTask2
    Try
        oStep = oPackage.Steps.New
        oTask = oPackage.Tasks.New("DTSTransferObjectsTask")
        oCustomTask = oTask.CustomTask
        oPackage.FailOnError = False
        With oStep
            .Name = "Copy Database design and data"
            .ExecuteInMainThread = True
        End With
        With oTask
            .Name = "PkgTask"
        End With
        With oCustomTask
            .Name = "DTSTransferObjectsTask"
            .SourceServer = "(local)"
            .SourceUseTrustedConnection = True
            .SourceDatabase = "pubs"
            .DestinationServer = "(local)"
            .DestinationUseTrustedConnection = True
            .DestinationDatabase = "pubs2"
            .CopyAllObjects = True
            .IncludeDependencies = False
            .IncludeLogins = False
            .IncludeUsers = False
            .DropDestinationObjectsFirst = True
            .CopySchema = True
            .CopyData = DTS.DTSTransfer_CopyDataOption.DTSTransfer_ReplaceData
        End With
        oStep.TaskName = oCustomTask.Name
        oPackage.Steps.Add(oStep)
        oPackage.Tasks.Add(oTask)
        oPackage.Execute()
    Catch ex As Exception
        MsgBox("Package failed error: " & vbCrLf & ex.Message & _
               vbCrLf, vbExclamation, oPackage.Name)
    Finally
        oCustomTask = Nothing
        oTask = Nothing
        oStep = Nothing
        oPackage.UnInitialize()
    End Try
End Sub

在 Visual Basic 中处理 DTS 错误

DTS 应用程序通常包括两个阶段

在第一阶段，应用程序创建 DTS 对象，设置其属性，并将它们添加到父对象的集合中。在对象创建/属性定义阶段发生的错误可以通过典型的 Visual Basic 错误处理程序进行处理。
在第二阶段，调用 `Package2` 对象的 `Execute` 方法。除非将 `Package2` 对象的 `FailOnError` 属性设置为 True，否则在 `Execute` 期间发生的错误不会传播回调用者。当 `FailOnError` 为 True 时，返回的错误描述通常只会告诉您包因（已命名）步骤失败而失败。为了确定步骤失败的原因，`Step` 对象的 `GetExecutionErrorInfo` 方法将返回一个 Visual Basic 错误对象的属性，该对象描述了错误。

下面的代码示例是一个基本的错误处理程序，可以在包开发过程中使用，并且 `FailOnError` 设置为 True。如果一次错误就使包失败是不受欢迎的，那么仍然可以使用 `sAccumStepErrors` 函数，但需要对其进行调用，作为对 `objPackage.Execute` 的正常返回，以及从错误处理程序中调用。

Private Sub RunPackage()
    'Run the package stored in file
    Dim objPackage As DTS.Package2
    Dim objStep As DTS.Step
    Dim oStep As DTS.Step
    Dim objTask As DTS.Task
    Dim objExecobjPkg As DTS.ExecutePackageTask


    objPackage = New DTS.Package
    objPackage.FailOnError = True

    'Create the step and task. Specify the package 
    'to be run, and link the step to the task.
    objStep = objPackage.Steps.New
    objTask = objPackage.Tasks.New("DTSExecutePackageTask")
    objExecobjPkg = CType(objTask.CustomTask, DTS.ExecutePackageTask)
    objExecobjPkg = objTask.CustomTask

    With objExecobjPkg
        ''.PackagePassword = "user"
        .FileName = My.Application.Info.DirectoryPath & _
                    "\DTSPakages\Import_Stores_DTS.dts"
        .Name = "ExecobjPkgTask"
    End With

    With objStep
        .TaskName = objExecobjPkg.Name
        .Name = "ExecobjPkgStep"
        .ExecuteInMainThread = True
    End With
    objPackage.Steps.Add(objStep)
    objPackage.Tasks.Add(objTask)

    'Run the package and release references.
    Try
        objPackage.Execute()
    Catch ex As Exception
        For Each oStep In objPackage.Steps
            If oStep.ExecutionStatus = _
                     DTS.DTSStepExecStatus.DTSStepExecStat_Completed Then
                If oStep.ExecutionResult = _
                         DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
                    Dim sMessage As String
                    Dim lErrNum As Long
                    Dim sDescr As String
                    Dim sSource As String

                    objStep.GetExecutionErrorInfo(lErrNum, sSource, sDescr)
                    sMessage = sMessage & vbCrLf & _
                            "Step " & oStep.Name & " failed, error: " & _
                            sErrorNumConv(lErrNum) & vbCrLf & sDescr & vbCrLf
                End If
            End If
        Next
    End Try

    ''Dim d As Double = objStep.ExecutionTime

    objExecobjPkg = Nothing
    objTask = Nothing
    objStep = Nothing

    objPackage.UnInitialize()
End Sub