SQL Server BI Step by step 4-2 合并数据 LookUp组件和Script Component组件完成数据合并

2011/6/13 23:10:59中文系列评论(0)浏览(1322)

合并数据2
----LookUp组件和Script Component组件完成数据合并

本章实现Excel中的数据与数据库中的数据进行整合时,存在即更新,不存在即插入.这次主要介绍几种方法来实现:
     1.使用Lookup
     2.使用execute SQL task调用存储过程
     3.使用script component脚本实现
     4.使用MERGE 语句(SQL SERVER 2008)
     5.使用上次我们用到的Merge来实现
     6.使用第三方组件SCD Component
看起来能够实现的方法确实不少,我们来一一介绍,介绍的同时也会介绍一些组件的应用,同理在我们实现其它功能时,也可以同样使用.

一、 LookUp组件
目的：实现遍历多个Excel源的同时，实现Excel中数据和数据库中数据合并，即：存在数据更新，不存在数据插入。

第二次循环，可以测试更新数据哦！同理。

2. 新建一个包MergeDataLookUp。
3. 新建一个Foreach循环容器，在里放入数据流任务，进行编辑。按照《Step3 批量导入Excel》配置该循环。注意配置变量和修改Foreach的属性哦！！！

4. 切换到数据流tab页，拖一个excel源连接Product1.xls、LookUp组件、OLE DB Command组件、派生列组件、OLE DB目标组件连接Product表用于存储“错误”纪录，并插入该纪录。

5. 对LookUp组件设置。
对tab引用表设置：

tab列设置：
通过 Excel数据中的ProductNumber字段去数据库中查找对应的数据（查找的纪录行数等于Excel数据源的行数）；
选择ProductID为LookUp查询后数据流显示的列。
当找不到对应数据时，点击上面的标出的配置错误输出。

对“配置错误输出”设置

若不设置，则会报错：
-----------------------------------------------报错---------------------------------------------------------------------------------------
错误: 0xC020901E，位于数据流任务, 查找 [39]: 在查找期间行没有生成任何匹配项。
错误: 0xC0209029，位于数据流任务, 查找 [39]: “组件“查找”(39)”失败，错误代码为 0xC020901E，而且针对“输出“查找输出”(41)”的错误行处理设置指定一旦出错就失败。在指定组件的指定对象上出错。
--------------------------------------------------------------------------------------------------------------------------------------------

6. 对OLE DB Command组件添加Update命令来控制输出。

UPDATE Production.Product SET [Name]=?,MakeFlag=? WHERE ProductID=?

设置UPDATE列字段与参数的映射。一定要将LookupID对应参数！参数怎么来的呢？呵呵，就是你刚刚Update语句中的参数。

7. 设置派生列组件

8. 设置OLE DB ,把RroductID、rowguid设置为忽略。Name,ProductNumber映射为派生类的新列。

完成，这8个步骤后，加入你关心的数据流查看器，就可以以执行包：
第一次，执行成功！

第二次，运行。

数据库中，数据库在第一次运行时，插入两行纪录

。

细心的朋友，可能会看出两次Lookup后查找到的更新输出、错误输出里的值都没有变化，而第一次执行后数据库的值就已经插入。而正常情况下，更新输出应该是4行了，而错误输出是0行！而整个运行没有错误！
WHY？猜想这个时候用了缓存或是SQL查找后的视图又或者？？，而导致没有真正去LOOKUP新数据库而直接读出数据。那这怎么办呢？问题哦！

二、使用存储过程
使用存储过程实现这个就相当容易的多,不过并不建议这样做,我们把所有的流程都放在了存储过程里面,而不是SSIS包里面,有一个应该考虑的是,一般我们可以在SSIS包里设置成支持事务(设置包或者组件的TransactionOption属性).在存储过程里,我们可以直接采用存储过程里面的事务机制.
我们新建一个包MergeDataProcedure,完成如上包的设置, 只需要执行一个OLE DB Command,在这里我们调用存储过程,存储过程里我们只是完成根据ProductNumber判断数据存在不存在,存在即执行更新,不存在插入.在这里就不再对这个方法进行详细介绍了.

三、使用Script Component
1. 新建一个包MergeDataScript。
2. 添加控制流和Excel源组件。
3. 添加Script Component组件。
选择脚本控件要使用——转换

然后设置输然后设置输入输出。有一个输入,三个输出命名为UpdateRecordsOutput,InsertRecordsOutput,IgnoreRecordsOutput,手动配置。注意到输入RecordsInput的ID为115.将输出的属性ExclusionGroup设置成1,将属性SyncronousInputID值设置成RecordsInput的ID为115。（注意：每个输入控件的SyncronousInputID都是不一样的.设置这两个属性是我们下面脚本运行的关键,具体将查询官方文档）

编辑连接管理器。
因为从脚本中要获取数据库连接,这里需要的是SqlDataReader,新建一个ADO.NET连接。
建立ADO.NET连接

脚本连接管理器连接到新建的ADO.NET

编辑脚本

脚本

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

Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient

Public Class ScriptMain
Inherits UserComponent

Dim connMgr As IDTSConnectionManager90

Dim sqlConn As SqlConnection

Dim sqlCmd As SqlCommand

Dim sqlParam As SqlParameter

Public Overrides Sub PreExecute()
sqlCmd = New SqlCommand("SELECT [Name] FROM Product WHERE ProductNumber=@ProductNumber", sqlConn)

sqlParam = New SqlParameter("@ProductNumber", SqlDbType.NVarChar, 25)

sqlCmd.Parameters.Add(sqlParam)

End Sub

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.DBConnection

sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

End Sub

    Public Overrides Sub 输入0_ProcessInputRow(ByVal Row As 输入0Buffer)
        Dim reader As SqlDataReader
        sqlCmd.Parameters("@ProductNumber").Value = Row.ProductNumber

        reader = sqlCmd.ExecuteReader()
        If reader.Read() Then
            '此处可以根据需要进行字段的比较
            If (reader("Name").ToString() <> Row.Name) Then
                Row.DirectRowToUpdateRecordsOutput()
            Else

Row.DirectRowToIgnoreRecordsOutput()

End If

        Else
            Row.DirectRowToInsertRecordsOutput()
        End If

        reader.Close()
    End Sub
    Public Overrides Sub ReleaseConnections()
        connMgr.ReleaseConnection(sqlConn)
    End Sub

End Class

上面的脚本的具体含义也就不再详细介绍,比较容易理解,其实与我们使用LookUp实现的功能相同.使用ProductNumber进行查找Name字段,如果找到Name则跳转到更新输出,否则跳转到忽略输出,如果没有找到,则跳转到添加输出.我们也可以直接把添加,更新这些操作放在脚本里面.不过为了使整个流程更加清晰,我们只是使用脚本进行了一个转换.不过其实脚本实现的会更加灵活,这里其实还可以实现双向查找或者是更加复杂的功能.

4. 在ScriptComponent设置了三个输出，UpdateRecordsOutput对应OLE DBCommand组件,InsertRecordsOutput对应OLE DB目标组件,IgnoreRecordsOutput对应行计数组件

5. 设置OLE DBCommand组件。