求含COZYROC Table Difference组件的SSIS包BIML生成代码示例

以下是完整的BIML代码示例，包含数据源配置、数据流以及COZYROC Table Difference组件的完整配置：

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <!-- 源数据库连接配置 -->
        <OleDbConnection Name="SourceDB" ConnectionString="Data Source=.;Initial Catalog=SourceDB;Integrated Security=SSPI;" />
        <OleDbConnection Name="TargetDB" ConnectionString="Data Source=.;Initial Catalog=TargetDB;Integrated Security=SSPI;" />
    </Connections>

    <Packages>
        <Package Name="TableDifferenceDemo" ConstraintMode="Linear">
            <Tasks>
                <Dataflow Name="DF_CompareTableDifferences">
                    <Transformations>
                        <!-- 读取新数据源（源表） -->
                        <OleDbSource Name="Src_NewData" ConnectionName="SourceDB">
                            <DirectInput>SELECT ID, ProductName, Price FROM dbo.SourceProducts</DirectInput>
                        </OleDbSource>
                        <!-- 读取旧数据源（目标表） -->
                        <OleDbSource Name="Src_OldData" ConnectionName="TargetDB">
                            <DirectInput>SELECT ID, ProductName, Price FROM dbo.TargetProducts</DirectInput>
                        </OleDbSource>

                        <!-- 多播组件（用于保留原始数据流，可选） -->
                        <Multicast Name="Multicast_NewData" />
                        <Multicast Name="Multicast_OldData" />

                        <!-- COZYROC Table Difference 核心组件 -->
                        <CustomComponent Name="CD_TableDifference" 
                            ComponentTypeName="CozyRoc.SqlServer.SSIS.TableDifference, CozyRoc.SSISPlus.2019, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea" 
                            Version="3">
                            <CustomProperties>
                                <!-- 键列与对比列属性配置 -->
                                <CustomProperty Name="NewInputLineageIDs" DataType="Int32" IsArray="true" ContainsId="true">
                                    <ArrayValue>100</ArrayValue> <!-- 替换为NewData中ID列的Lineage ID -->
                                    <ArrayValue>101</ArrayValue> <!-- 替换为NewData中ProductName列的Lineage ID -->
                                    <ArrayValue>102</ArrayValue> <!-- 替换为NewData中Price列的Lineage ID -->
                                </CustomProperty>
                                <CustomProperty Name="OldInputLineageIDs" DataType="Int32" IsArray="true" ContainsId="true">
                                    <ArrayValue>200</ArrayValue> <!-- 替换为OldData中ID列的Lineage ID -->
                                    <ArrayValue>201</ArrayValue> <!-- 替换为OldData中ProductName列的Lineage ID -->
                                    <ArrayValue>202</ArrayValue> <!-- 替换为OldData中Price列的Lineage ID -->
                                </CustomProperty>
                                <CustomProperty Name="KeyOrders" DataType="Int32" IsArray="true">
                                    <ArrayValue>0</ArrayValue> <!-- ID列升序 -->
                                    <ArrayValue>0</ArrayValue> <!-- ProductName列升序 -->
                                    <ArrayValue>0</ArrayValue> <!-- Price列升序 -->
                                </CustomProperty>
                                <CustomProperty Name="UpdateIDs" DataType="Int32" IsArray="true">
                                    <ArrayValue>0</ArrayValue> <!-- ID列作为匹配键 -->
                                    <ArrayValue>1</ArrayValue> <!-- ProductName参与更新检查 -->
                                    <ArrayValue>1</ArrayValue> <!-- Price参与更新检查 -->
                                </CustomProperty>
                                <CustomProperty Name="CheckOptions" DataType="Int32" IsArray="true">
                                    <ArrayValue>0</ArrayValue> <!-- ID列仅作为匹配键 -->
                                    <ArrayValue>1</ArrayValue> <!-- ProductName检查键+值 -->
                                    <ArrayValue>1</ArrayValue> <!-- Price检查键+值 -->
                                </CustomProperty>
                                <CustomProperty Name="Names" DataType="String" IsArray="true">
                                    <ArrayValue>ID</ArrayValue>
                                    <ArrayValue>ProductName</ArrayValue>
                                    <ArrayValue>Price</ArrayValue>
                                </CustomProperty>

                                <!-- 字符串对比规则配置 -->
                                <CustomProperty Name="StringCompareCultureId" DataType="Int32">0</CustomProperty>
                                <CustomProperty Name="StringCompareIgnoreCase" DataType="Boolean">false</CustomProperty>
                                <CustomProperty Name="StringCompareIgnoreKana" DataType="Boolean">false</CustomProperty>
                                <CustomProperty Name="StringCompareIgnoreWidth" DataType="Boolean">false</CustomProperty>
                                <CustomProperty Name="StringCompareIgnoreNonSpace" DataType="Boolean">false</CustomProperty>
                                <CustomProperty Name="StringCompareIgnoreSymbols" DataType="Boolean">false</CustomProperty>
                                <CustomProperty Name="StringCompareSort" DataType="Boolean">false</CustomProperty>
                                <CustomProperty Name="EnableLogOutput" DataType="Boolean" TypeConverter="NOTBROWSABLE">false</CustomProperty>
                                <CustomProperty Name="IncludeInputColumnsInLogOutput" DataType="Boolean">true</CustomProperty>
                            </CustomProperties>

                            <Annotations>
                                <Annotation AnnotationType="Description">对比新旧产品表的记录差异</Annotation>
                            </Annotations>

                            <!-- 输入路径绑定 -->
                            <InputPaths>
                                <InputPath OutputPathName="Multicast_NewData.Output" SsisName="New Data Flow" Identifier="NEW" />
                                <InputPath OutputPathName="Multicast_OldData.Output" SsisName="Old Data Flow" Identifier="OLD" />
                            </InputPaths>

                            <!-- 差异结果输出配置 -->
                            <Outputs>
                                <Output Name="New" Description="仅存在于源表的新记录" />
                                <Output Name="Changed" Description="键匹配但值不同的变更记录" />
                                <Output Name="Old" Description="仅存在于目标表的旧记录" />
                                <Output Name="Unchanged" Description="新旧表完全匹配的记录" />
                            </Outputs>
                        </CustomComponent>

                        <!-- 示例：将差异结果写入对应表 -->
                        <OleDbDestination Name="Dst_NewProducts" ConnectionName="TargetDB">
                            <InputPath OutputPathName="CD_TableDifference.New" />
                            <ExternalTableOutput TableName="dbo.NewProducts" />
                        </OleDbDestination>
                        <OleDbDestination Name="Dst_ChangedProducts" ConnectionName="TargetDB">
                            <InputPath OutputPathName="CD_TableDifference.Changed" />
                            <ExternalTableOutput TableName="dbo.ChangedProducts" />
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

关键配置说明 #

1. Lineage ID 替换：NewInputLineageIDs和OldInputLineageIDs的数组值需要替换为实际输入列的Lineage ID，可以通过Visual Studio生成SSIS包后查看组件属性，或使用BIML动态API（如GetColumnLineageID）获取。
2. 版本适配：ComponentTypeName中的CozyRoc.SSISPlus.2019需与你安装的COZYROC SSISPlus版本一致（如2017版对应CozyRoc.SSISPlus.2017）。
3. 输出路径：Table Difference组件默认提供4种差异输出，可根据业务需求选择使用或扩展。

内容的提问来源于stack exchange，提问作者DrLechter