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

 

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _

<CLSCompliant(False)> _

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中的一些可以编写脚本的任务，使用表达式动态设置属性，使用表达式语言来完成数据转换的任务，如何使用脚本任务控制工作流，使用脚本组建清洗数据。通过一些练习我们可以更好地掌握如何使用脚本。