如何在 Microsoft SSIS (DTS) 包中使用全局变量

• 下载 global_variable_in_SSIS.zip - 2.26 KB

引言

Microsoft SQL Server 2005 Integration Services 有一个新的关于 DTS（数据转换服务）的界面。它没有很多有用的例子供开发人员参考。其中一个棘手的问题是如何使用全局变量。希望以下内容可以为您节省大量的精力。

场景如下：（1）。我想将数据从一个表（table1，主键是 theId - int，字段是 FirstName - varchar(50)，LastName - varchar(50)）从数据库 1 (db1) 转储到另一个相同结构的表（table2，主键是 theId - int，字段是 FirstName - varchar(50)，LastName - varchar(50)），数据库 2 (db2)，基于 table1.theId > max(table2.theId)。 这是一个非常常见的数据传输场景。(2). 我需要将 table2.theId 传递给 SQL 语句：SELECT * FROM table1 WHERE table1.theId > max(table2.theId) (3) 你需要确保 table1 有更多的行，并且 TheId 大于 table2

我们可以将整个 SQL 语句作为一个全局字符串变量传递，而不是将 table2.theId 视为一个变量传递。

以下是如何实现此目的的步骤

1. 启动 Microsoft SQL Server 2005 -> SQL Server Business Intelligence Development Studio
2. 点击 文件 -> 新建 -> 项目 -> 选择“Integration Services 项目” -> 给你的项目命名，比如 ssisTest
3. 点击 SSIS -> 变量
4. 在变量窗口下，添加变量

   name = strSQL
   scope = Package ( this means global variable for this package )
   Data Type = string
   value = select * from table1

5. 在左下角，你会看到连接管理器，将光标放在该区域，右键单击以添加新的 OLE DB 连接。 您为 db1 添加一个连接，为 db2 添加另一个连接。
6. 点击“控制流”选项卡，ctrl+alt+x 获取工具箱，将“脚本任务”从“控制流项”拖到“控制流”选项卡。高亮显示“脚本任务 1”，然后双击“脚本任务 1”图标 -> 高亮显示“脚本任务编辑器”左侧面板上的“脚本” -> 为 ReadWriteVariables 输入“strSQL” -> 点击“设计脚本...”
7. 以下是代码

   ' Microsoft SQL Server Integration Services Script Task
   ' Write scripts using Microsoft Visual Basic
   ' The ScriptMain class is the entry point of the Script Task.
   
   Imports System
   Imports System.Data
   Imports System.Math
   Imports Microsoft.SqlServer.Dts.Runtime
   Imports System.Data.SqlClient
   Imports System.Data.Common
   
   Public Class ScriptMain
   
   ' The execution engine calls this method when the task executes.
   ' To access the object model, use the Dts object. Connections, variables, events,
   ' and logging features are available as static members of the Dts class.
   ' Before returning from this method, set the value of Dts.TaskResult to
   ' indicate success or failure.
   ' 
   ' To open Code and Text Editor Help, press F1.
   ' To open Object Browser, press Ctrl+Alt+J.
   
   Public Sub Main()
   '
   ' Add your code here
   '
   Dim strConnection As String
   strConnection = "Data Source=server2;Initial Catalog=db2;Integrated Security=True"
   Dim connection As New SqlConnection(strConnection)
   connection.Open()
   Dim strQuery As String
   strQuery = "select max(TheId) MaxTheId from Table2"
   Dim command As New SqlCommand(strQuery, connection)
   Dim srReader As SqlDataReader
   srReader = command.ExecuteReader()
   
   Dim strTheId As String
   ' strTheId = "5"
   
   While (srReader.Read())
   strTheId = srReader.Item(0).ToString
   End While
   
   connection.Close()
   
   ' here create the right strSQL string
   Dts.Variables("strSQL").Value = "SELECT * FROM TABLE1 WHERE TheId > " & strTheId
   
   ' this is to test if the strSQL string is right
   MsgBox(Dts.Variables("strSQL").Value.ToString)
   
   Dts.TaskResult = Dts.Results.Success
   End Sub
   
   End Class

   然后保存并关闭此窗口

8. 点击“数据流”选项卡，然后点击该选项卡中心的链接以添加一个数据流任务
9. 在“数据流”选项卡上，从工具箱的“数据流源”部分拖动“OLE DB 源”，双击此 OLE DB 源图标，然后在 OLE DB 源编辑器下，
   1. 为 OLE DB 连接管理器选择 server1.db1
   2. 数据访问模式：来自变量的 SQL 命令
   3. 变量名称：User::strSQL

   然后点击“确定”

10. 在“数据流”选项卡上，从工具箱的“数据流目标”部分拖动“OLE DB 目标”，双击此 OLE DB 目标图标，然后在 OLE DB 目标编辑器下，
    1. 为 OLE DB 连接管理器选择 server2.db2
    2. 数据访问模式：表或视图 - 快速加载
    3. 表或视图的名称：[dbo].[table2]

    然后点击左侧窗格中的“映射”，并确保所有字段都正确映射，然后点击“确定”

11. 现在您可以点击“生成” -> “生成 ssisTest”，然后点击“调试” -> “开始调试”
12. 您可以验证从 db1 的 table1 插入到 db2 的 table2 的数据

这样您就成功地在 Microsoft SSIS (DTS) 包中使用了全局变量。

请享用。