[译]Stairway to Integration Services Level 13 - SSIS 变量回顾

介绍

在前一篇中我们组合了已经学过的事件冒泡 event bubbling, 日志记录 logging, 和父子模型 Parent-Child pattern 建立了自定义的SSIS包日志记录.

本文中, 我们会将我们的解决方案升级至 SQL Server 2012 Integration Services 并且展示 SSIS变量 (SSIS variables), 变量(variable configuration), 以及通过表达式处理动态值 (dynamic value management via expressions). 在之前的联系中我们已经用了多次SSIS变量.但是我们并未深入研究.本篇文章我们会专注于SSIS变量.

开始使用 VS2012 进行SSIS 开发  Getting Starting with the Visual Studio 2012 IDE for SSIS 2012 Development

新建一个项目My_Second_SSIS_Project (注意我用的还是VS2013….):

或许SSIS 2012 最大的变化就是有了一个新的开发模式: 项目开发模式(Project Deployment Model). 当然SSIS 2012 也支持上一个版本的包开发模式 (Package Deployment Model).

在开始之前,我们先把 Package.dtsx 重命名为 VariablesAndParameters.dtsx :

Figure 10

注意现在你重命名 dtsx 文件的时候不会再有提示.

Beginning with Variables

打开变量窗口,然后添加一个变量 MyVariable 如图

Figure 12

注意SSIS 包 VariablesAndParameters 的变量作用域. 使用容器才代表作用域是比较好的方式. 下图我们可以看到三个容器:

1. Task
2. Container
3. Package

Figure 13

Tasks, Containers, 以及 Packages 都是可执行的. 在SSIS中一个可执行的对象具有属性和触发事件. 一个task总归放置在一个Container里面. “等等,Andy ! 如果我把 Execute SQL Task 直接放在 Control Flow上呢?” 好问题: Package 同样也是容器. SSIS提供其他一些容器,即 the Sequence Container, the For Loop Container, 和 the Foreach Loop Container.每个容器都可以放置task.  而且每个容器,包括包本身都是可执行的.

SSIS 也提供了其他方式来表示作用域. 我们来做个演示. 添加一个 Sequence Container到Control Flow界面,然后再加个 Execute SQL Task:

Figure 14

Package Explorer 用树型视图展示了作用域. 其实和 Figure 13表现的一样: 这个包 (VariablesAndParameters) 包含一个容器 (Sequence Container) 然后它有包含一个 Task (Execute SQL Task) (shown in Figure 15):

Figure 15

属性视图帮助我们呈现了我说的作用域中的“上” 和“下” . 从Execute SQL Task来看, Sequence Container 在他”上面”.

SSIS 2012 的变量默认作用域是包 .变量的作用域是可以改动的.不过到目前位置,我还未发现变量在包级别的作用域内有什么特别用处.  点击 Variables 窗口工具栏的第二个按钮 ,我们可以把变量移动到不同的作用域.

Figure 16

把作用域调整至 Sequence Container:

Figure 17

注意 Scope 已经变成了Sequence Container

Figure 18

在Control Flow中点击 Sequence Container ,MyVariable 变量就会在 Variables 窗口显示出来:

Figure 19

现在点击Control Flow的空白区域:

Figure 20

因为MyVariable的作用域不在包级别,所以此时变量窗口已经看不到这个变量了. 当然你可以更改显示设置,来改变这一行为:

Figure 21

点击上图中的 Grid Options 按钮,在弹出的窗口中选择 “Show variables of all scopes”:

Figure 22

点击OK按钮以后 MyVariable就会显示在变量窗口 :

现在删掉 Execute SQL Task,用 Script Task代替. 创建一个新的变量 MyVariable. 建立完毕,Scope默认为包级别(VariablesAndParameters):

Figure 24

为了演示,我把两个MyVariable设置为不同值.

双击Script Task打开编辑器,然后设置 ScriptLanguage 为 Microsoft Visual Basic 2012. ReadOnlyVariables里面选择 MyVariable 变量:

Figure 25

选好 MyVariable 变量后点击OK 按钮, Script Task editor 显示如下:

Figure 26

点击 Edit Script 按钮,在Public Sub Main() 子程序里面添加以下代码:

MsgBox(Dts.Variables("User::MyVariable").Value.ToString)

Listing 1

现在script editor 显示如下:

Figure 27

关闭脚本编辑器以后,执行包,看看显示哪个值.

Figure 28

为什么会显示42? 还记得我之前提到的执行栈( execution stack )么.想一下事件如何在这些可执行文件中传递的 ). 还记得他们是怎么工作的么.  事件一步步晚上走,我称之为事件冒泡.

变量行为也是类似. 在 Script Task 执行前, SSIS试图锁定 MyVariable 变量. 为什么要锁定它? 想象一下如果两个可执行文件同时使用同一个变量会怎么样? 假设一个写这个变量,另外一个是读? 算上所有可能,我 希望在读取变量的时候这个值是不变的. 为了确保这个值不变. SSIS给变量上锁. 在可执行文件操作的过程中它的值无法被变更.

想象一下SSIS的锁定机制 (“keyholder” for the purposes of this analogy), 轮询执行栈中MyVariable变量. keyholder 从 Script Task 开始询问t, “Script Task, 你有没有一个名叫MyVariable的变量?”  Script Task 回应道, “没有.” 然后 keyholder 在执行栈中迈了一步. 并且询问Sequence Container, “Sequence Container, 你有没有一个名叫MyVariable的变量?”  Sequence Container 回复, “有的.” 然后 keyholder 停止锁定. It has found the variable it sought. Or did it?

如果我的变量隐藏了,我可能没意识到我有两个不同作用域的同名变量 MyVariable . 我可能不小心把MyVariable 作用域改成了 Sequence Container 但是忘了这件事情. 然后又创建了一个包级别作用域的 MyVariable 变量 … ( 我们刚才就创建了这个变量).

此外, 当你把SSDT关掉以后 “Show variables of all scopes”就会复原 .

这就是我为什么不喜欢默认情况下.变量在不同作用域会隐藏的原因: 我没法在Script Task 中接触包作用域(package-scoped )的变量MyVariable .  它不会显示在ReadOnlyVariables的列表中. 我也没法指定 “VariablesAndParameters.User::MyVariable”. 根本没这样的选项,并且除非我开启了“Show variables at all scopes”,否则我不知道它的存在.

变量数据类型 Variable Data Types

SSIS变量有一些可用的数据类型: various numeric data types, date, byte, Boolean, string, and character. 最有意思的应当是Object 数据类型:

Figure 29

SSIS 的 Object 数据类型可以容纳很多值.包括单个整数,字符和日期 .同样也可以容纳对象objects; 比如集合,数组,记录集和数据集.( recordsets and datasets). 我不会一一演示,不过我建议你看看我写的一篇文章 SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers.

Building Connections Using Variables

让我们学以致用 . 变量可以用来组建其他变量值.   让我们用变量创建平文件的连接吧.

先创建一个平面文件 Songs.csv:

Id,Artist,Song
"0","Waylon Jennings","Lonesome, On'ry, and Mean"

"1","Willie Nelson","Blue Eyes Cryin' in the Rain"

"2","Kris Kristofferson","Sunday Mornin', Coming Down"

Listing 2

添加一个 Data Flow Task 并与 Sequence Container 连起来:

Figure 39

打开Data Flow Task 并添加一个 Flat File Source 适配器:

Figure 40

打开 Flat File Source 编辑器,然后建立一个新的平面文件连接管理:

Figure 41

Flat File Connection Manager Editor 显示以后 “Connection manager name” 属性设置为“Songs Flat File” 然后“File name”属性定位到之前创建的文件. “Text qualifier” 属性这边打上双引号 :

Figure 42

点击OK 按钮关闭 Flat File Connection Manager Editor.显示如下:

Figure 43

点击 OK 按钮关闭Flat File Source Editor.

创建一个“TestDB” 数据库 :

Use master
go
If Not Exists(Select name              From sys.databases
              Where name = 'TestDB')
 begin
  print 'Creating TestDB'
  Create Database TestDB
  print 'TestDB created'
 end
Else
 print 'TestDB already exists'
go

Listing 3

Listing 3 是一个幂等脚本(idempotent script)的例子. 我从Jamie Thomson (blog | @jamiet).那边学了这个词, Jamie 说这个名词的意思是可重复执行的代码(re-executable code).

SSDT里面再拖个 OLE DB Destination 过去:

Figure 44

前面创建连接的过程省略. 点击 “Name of the table or view” 属性的new 按钮 ,然后弹出来一个窗口,显示了一个从元数据数据流生成的 DDL语句( Database Definition Language ). 把表名改为“Songs”.注意这里面的列也来自数据流路径 :

Figure 48

点击OK以后显示如下:

Figure 49

OLE DB Destination Editor,出现警告信息:

Figure 50

点击 Mappings page 会自动匹配:

Figure 51

你可能会提问,什么是自动映射(auto-mapping)?   Available Input Columns表示从data flow path传进来数据流的 schema . Available Destination Columns 表示目标表或者视图中的列 .那么OLE DB Destination 是怎么活的元数据来创建表的呢?

让我们打开 Data Flow Path ,然后点击 Metadata page显示 data flow path的结构:

Figure 52

Metadata 里面有列名,数据类型,以及长度. 当我们点击New按钮创建新表格的时候,这些元数据提供了schema信息.

同样的 Available Destination Columns (shown in Figure 51) 也是有这些信息生成的, 然后根据这些信息来自动匹配

变量表达式  Variables in Expressions

在SSIS变量窗口里新建一个FileDirectory的包级别变量 . Value属性就填上 Songs.csv 所在的目录:

Figure 53

在创建一个 FileName 变量,并且把值设为“Songs.csv”:

Figure 54

在创建一个  FilePath :

Figure 55

接着设置一下 FilePath的表达式

Variables in Dynamic Property Expressions

我们现在有一个变量 – FilePath – 包含了源文件的完整路径, 是由表达式生成的.其中包含 FileDirectory 和 FileName的值.

俺么接下来干什么呢? 让我们用那个完整路径来动态管理我们 Flat File Connection Manager (“Songs Flat File”) 对应的文件. 点击connection manager 里面的 Songs Flat File ,按F4把属性面板调用出来

Figure 62

点击Expressions旁边的省略号. 点击属性下拉框,选择 ConnectionString 属性:

Figure 63

点击 Expression 属性的省略号 把FilePath 变量拖进去:

Figure 64

完成以后如下

Figure 65

介绍断点和变量状态  Introducing Breakpoints and Variable State

断点用来调试非常方面 ,在我们测试之前,先点击 Data Flow Task 然后右击选择k “Edit Breakpoints…”本例中当我们会Data Flow Task 触发PreExecute事件的时候设置一个断点来暂停执行

Figure 66

Set Breakpoints 里面选择“Break when the container receives the OnPreExecute event” :

Figure 67

点击确认后,断点会显示在Dtat Flow Task图标上面

Figure 68

执行包, 我们会发现当 Data Flow Task 触发 PreExecute 事件以后暂停了运行:

Figure 69

点击DEBUG菜单 –> Windows –> Locals来查看 FilePath的状态:

Figure 70

展开 Variables 节点.定位到 User::FilePath 变量:

Figure 71

按 F5 或者点击 Continue (Play) 按钮继续执行,最后我们导入了三行数据:

Figure 72

原文链接 http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/99720/

项目文件 My_First_SSIS_Project_After_Step_13.rar