ie脚本支持组件,SSIS中的脚本—脚本组件

脚本组件提供另外一种在SSIS中使用脚本的方法，它只能在Data Flow中使用，不能在Control Flow中使用，它用来提供数据，接收数据，转换数据。下面是三种类型：
数据源类型：用来提供数据源，可以定义输出类型，使用脚本填充数据。了个典型的例子是使用脚本来读取复杂的文件，XML，或者更加过时的COBOL等等不能产生平面文件的文件 数据目的类型：用来将数据填充到Excel，或者平面文件，或者将文件批处理到大型机上 转换型：用来接收数据，产生新的转换数据，当SSIS提供的组件不能满足需求的时候使用
使用脚本组件
这里我们举例说明如何创建和使用脚本组建，我们处理一个文件，按照需求清洗文件中的数据。符合要求的数据将会被送到合适的表中，反之被送到另外一个表中。 我们处理一个包含联系人信息的文件，数据库对文件数据有一些验证要求，不符合验证标准的数据将会被送到另外一个表中人工处理。 使用下面的代码来建立这两个表：
CREATE TABLE dbo.Contacts ( ContactID int NOT NULL IDENTITY (1, 1), FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, City varchar(25) NOT NULL, State varchar(15) NOT NULL, Zip char(10) NULL ) ON [PRIMARY] CREATE TABLE dbo.ContactsErrorQueue ( ContactErrorID int NOT NULL IDENTITY (1, 1), FirstName varchar(50) NULL, LastName varchar(50) NULL, City varchar(50) NULL, State varchar(50) NULL, Zip varchar(50) NULL )

两个表的区别是ContactsErrorQueue表中的数据类型都是varchar(50)，并且可为空。下载Contacts.dat文件，这个文件中的数据边界分割位置如下：
Field Starting Position FirstName 1 LastName 10 City 25 State 43 Zip 51

数据的个数类似下面
Jason Gerard Jacksonville FL 32276-1911
Joseph McClung JACKSONVILLE FLORIDA 322763939
Andrei Ranga Jax fl 32276
Chad Crisostomo Orlando FL 32746
Andrew Ranger Jax fl

新建一个package命名为ScriptComponent，拖放一个Flat File source，双击打开编辑界面，点击新建连接，打开文件连接管理界面，将文件连接命名为Contacts Mainframe Extract。点击浏览选择Contacts.dat文件，选择文件格式为Fixed Width，设置行宽为62，按照上面标设置列边界。点击Advance，按照上表为数据列命名。最后预览数据如下图1：


图1
新建一个package命名为ScriptComponent，拖放一个Flat File source，双击打开编辑界面，点击新建连接，打开文件连接管理界面，将文件连接命名为Contacts Mainframe Extract。点击浏览选择Contacts.dat文件，选择文件格式为Fixed Width，设置行宽为62，按照上面标设置列边界。点击Advance，按照上表为数据列命名。最后预览数据如下图2：

图2
将Flat File Source和Script Component连接起来，双击script component打开编辑界面，点击Input Column标签，可以看到在Input Name下拉列表中默认选择Input 0，如果有其他输入的话，这里可以选择其他的输入，如图3。

图3
选择所有的输入列。点击Input and Output标签，查看输入列和输出列的属性。展开Output 0，选中Output Columns，点击Add Column按钮添加新列，命名为GoodFlag，修改数据类型为Boolean[DT_BOOL]。 点击Edit Script按钮打开编解界面，这里的代码和Script task有所不同
' Microsoft SQL Server Integration Services Script Component
' Write scripts using Microsoft Visual Basic 2008.
' ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
''
Return

End Sub

End Class
引入的命名空间有所不同，添加了两个新的命名空间Pipeline和Wrapper。ScriptMain类继承UserComponent。没有Mian()方法了，而是新的方法Input0_ProcessInputRow，它的参数Input0Buffer是一个自动产生的类，它包含所有的输入输出列，都有强类型的属性。 这里一共产生了3个源文件：BufferWrapper，ComponentWrapper，ScriptMain。BufferWrapper包含自动产生的代码缓冲，在这个例子中只有一个类Input0Buffer，它是自动产生的每次打开都回自动覆盖，所以不要修改这里的代码。文件ComponentWrapper中包含类UserComponent，主要的功能包含在类ScriptMain中。
要进行的验证有： • 除了字段zip，所有的字段都要求非空 • Zip的格式要求DDDDD-DDDD或 DDDD，这里D是一个0至9的数字，如果前5个数字验证成功，后面没有成功则截取前5个，后面的舍弃 • State必须是两位大写字符
这里使用正则表达式来验证，如下： • ^\d{5}([\-]\d{4})?$：验证5位或者9位的邮政编码 • \b([A-Z]{2})\b ：验证2位的state
添加如下的代码段：

' Microsoft SQL Server Integration Services Script Component
' Write scripts using Microsoft Visual Basic 2008.
' ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Text.RegularExpressions

_
_
Public Class ScriptMain
Inherits UserComponent

Private zipRegex As Regex = New Regex("@^\d{5}([\-]\d{4})?$", RegexOptions.None)
Private stateRegex As Regex = New Regex("@\b([A-Z]{2})\b", RegexOptions.None)
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'all fields except zip must have a value
Dim isGood As Boolean = True
If Row.FirstName_IsNull Or Row.LastName_IsNull Or Row.City_
Then
Row.GoodFlag = False
Return
End If

If Not Row.Zip_IsNull Then
Dim zip As String = Row.Zip.Trim()
'zip must match regex if present
If zipRegex.IsMatch(zip) Then
Row.CleanedZip = zip
isGood = True
Else
'try to clean up the zip

If zip.Length > 5 Then
zip = zip.Substring(0, 5)
If zipRegex.IsMatch(zip) Then
Row.CleanedZip = zip
isGood = True
Else
isGood = False
End If
End If
End If
End If

If isGood Then

Dim state As String
state = Row.State.Trim().ToUpper()
If stateRegex.IsMatch(state) Then
Row.CleanedState = state
Else
isGood = False
End If
End If
Row.GoodFlag = isGood

End Sub
End Class

这段代码执行验证规则，在方法Input0_ProcessInputRow内，首先检查字段是否为空，除了zip，这里使用了ColumnsName_IsNull属性，如果数据为空，它返回true。 下一步，如果Zip列不为空，并且通过正则表达式验证，将它的值赋给CleanedZip,以便保存到目标表中。如果没有通过正则表达式验证，代码检查Zip长度是否大于5，如果是，截取前5位并使用正则表达式验证，如果通过验证，赋值给CleanedZip，isGood赋值为true，否则的话isGood赋值为false。 去掉State的前空格和后空格，转换成大写，然后使用正则表达式来验证，如果通过验证将它的值赋值给CleanedState，否则isGood赋值为false。 根据isGood的值将数据送到相应的表中，拖放一个Conditional Split task，将script task和它连接起来。双击Conditional Split的编辑界面，使用条件GoodFlag == TRUE 添加一个输出。这样数据将会被分成两部分。 添加两个OLE DB Destination，一个指向Contacts表，一个指向ContactErrorQueue表。将Conditional Split和Contacts表连接起来。因为Conditional Split有多个输出，选择符合条件GoodFlag == TRUE的这一股，剩下的指向ContactsErrorQueue表。最后执行这个package如图4

图4
最后有14行数据进入到Contracts表中，4行数据进入到ContactsErrorQueue表中，如图5。

图5
调试脚本组件
在脚本任务这个章节我们讲述了如何使用断点 调试，在脚本组件中我们不能使用这个特性，在代码中设置的断点会被忽略，你不许诉诸 于Row Count或者Data Riewer。 Row Count的功能很明显，它显示有多少数据通过数据流，Data Viewer更加方便。点击Script Component和Conditional Split之间的连线 ，右击选择Data Viewers，在弹出的Data Flow Path Editor对话框中选择Data Viewer标签点击Add，选择Data Viewer Type为Grid，选择想要查看的列，最后点击保存。如图6

图6
再次运行这个package，将会看到Data Viewer界面，这里显示从Script Component中输出的数据，如图7。点击Play按钮程序继续运行，或关闭界面，程序也会继续运行下去。

图7
这里我们只能看到数据不能看到代码的执行情况。
总结
这一章中我们看到SSIS中的一些可以编写脚本的任务，使用表达式动态设置属性，使用表达式语言来完成数据转换的任务，如何使用脚本任务控制工作流，使用脚本组建清洗数据。通过一些练习我们可以更好地掌握如何使用脚本。

Tags: 

延伸阅读