使用完全高速缓存时，查找组件无法匹配空字符串 [英] Lookup component fails to match empty strings when full cache is used

问题描述

我有一个查找组件a，其中包含一个查找表，该表重新显示了 varchar(4)列，并具有3个可能的值:"T"，"R"或"(空字符串).

我正在使用OLE DB连接查找表，并尝试直接访问该表，并在列上使用 RTRIM()指定查询，以确保该字符串为空，而不是一定长度的空白字符串".

如果将缓存模式设置为部分缓存"，则一切正常(通过直接读取表或使用修整查询)，并且输入表的空字符串正确匹配了相应的查找表行

但是，如果我将缓存模式更改为全缓存"，则所有空字符串都不会被匹配.

我检查了查找表和输入表中的数据类型 DT_STR 和长度4是否相同.

有什么可以解释这种行为的吗?可以修改吗?

注意:这不是具有空值的已记录问题.这是关于空字符串.

解决方案

在某些地方，源代码或查找中都有尾随空格.

请考虑以下源查询.

  SELECTD.SourceColumnD.说明从(价值(CAST('T'AS varchar(4))，'T')，(CAST('R'AS varchar(4))，'R')，(CAST(''AS varchar(4))，'空字符串')，(CAST(''AS varchar(4))，'Blanks')，(NULL，'NULL'))D(SourceColumn，Description); 

对于我的查找，我将上面的查询限制为T，R和空字符串"行.

您可以看到，对于5个源行，T，R和Empty String匹配并转到Match Output路径.在我使用NULL或明确使用空格的地方，不是匹配了.

如果我将查找模式从完全缓存"更改为部分缓存"，则NULL继续不匹配，而显式空格匹配 .

会吗?

在完全缓存模式下，Lookup转换将执行源查询，并将数据本地保留在正在执行SSIS的计算机上.使用.NET相等规则，此查找将完全匹配.在这种情况下，''将与''不匹配.

但是，当我们将缓存模式更改为无"或部分"时，我们将不再依赖.NET匹配规则，而是使用源数据库的匹配规则.在TSQL中，'' 将匹配''

要使您的完全缓存模式按预期工作，您将需要在Source和/或Lookup转换中应用 RTRIM .如果您确信RTRIM无法使用您的源代码，请添加派生列转换，然后在其中应用RTRIM，但我发现最好滥用数据库而不是SSIS.

Biml

Bip，商业智能标记语言，描述了商业智能的平台. BIDS助手，是Visual Studio/BIDS/SSDT的免费附件，我们将使用它来进行转换下面的Biml文件放入SSIS包中.

以下biml将生成

 < Biml xmlns ="http://schemas.varigence.com/biml.xsd"><连接>< OleDbConnection Name ="CM_OLE" ConnectionString ="Data Source = localhost \ dev2012; Initial Catalog = tempdb; Provider = SQLNCLI11.0; Integrated Security = SSPI;"/></连接><包裹>< Package ConstraintMode ="Linear" Name ="so_26719974"><任务>< Dataflow Name ="DFT演示"><变换>< OleDbSourceConnectionName ="CM_OLE"名称="OLESRC源">< DirectInput>选择D.SourceColumnD.说明从(价值(CAST('T'AS varchar(4))，'T')，(CAST('R'AS varchar(4))，'R')，(CAST(''AS varchar(4))，'空字符串')，(CAST(''AS varchar(4))，'Blanks')，(NULL，'NULL'))D(SourceColumn，Description);</DirectInput></OleDbSource><查找名称="LKP POC"OleDbConnectionName ="CM_OLE"NoMatchBehavior ="RedirectRowsToNoMatchOutput">< DirectInput>选择D.SourceColumn从(价值(CAST('T'AS varchar(4)))，(CAST('R'AS varchar(4)))，(CAST(''AS varchar(4))))D(SourceColumn);</DirectInput><输入>< Column SourceColumn ="SourceColumn" TargetColumn ="SourceColumn"></Column></输入></查找>< DerivedColumns Name ="DER默认捕获器"/>< DerivedColumns Name ="DER NoMatch catcher">< InputPath OutputPathName ="LKP POC.NoMatch"/></DerivedColumns></Transformations></数据流></任务></包装></Packages></Biml> 

I have lookup component a with a lookup table that retusn a varchar(4) column with 3 possible values: "T", "R" or "" (empty string).

I'm using an OLE DB connection for the lookup table, and have tried direct access to the table, as well as specifying a query with an RTRIM() on the column, to get sure that the string is empty and not a "blank string of some length".

If I set the cache mode to "Partial cache" everything works fine (either with direct reading of the table, or using the trimming query), and the empty strings of the input table are correctly matched to the corresponding lookup table row.

However, If I change the cache mode to "Full cache", none of the empty strings are matched at all.

I've checked that the data type, DT_STR, and lenght, 4, is the same in the lookup table and the input table.

Is there something that explains this behaviour? Can it be modified?

NOTE: This is not the documented problem with null values. It's about empty strings.

解决方案

Somewhere, you have trailing spaces, either in your source or your lookup.

Consider the following source query.

SELECT
    D.SourceColumn
,   D.Description
FROM
(
    VALUES 
        (CAST('T' AS varchar(4)), 'T')
    ,   (CAST('R' AS varchar(4)), 'R')
    ,   (CAST('' AS varchar(4)), 'Empty string')
    ,   (CAST('    ' AS varchar(4)), 'Blanks')
    ,   (NULL, 'NULL')
) D (SourceColumn, Description);

For my lookup, I restricted the above query to just T, R and the Empty String rows.

You can see that for the 5 source rows, T, R and Empty String matched and went to the Match Output path. Where I used a NULL or explicitly used spaces, did not make a match.

If I change my lookup mode from Full Cache to Partial, the NULL continues to not match while the explicit spaces does match.

Wut?

In full cache mode, the Lookup transformation executes the source query and keeps the data locally on the machine SSIS is executing on. This lookup is going to be an exact match using .NET equality rules. In that case, '' will not match ' '.

However, when we change our cache mode to None or Partial, we will no longer be relying on the .NET matching rules and instead, we'll use the source Database's matching rules. In TSQL, '' will match ' '

To make your Full Cache mode work as expected, you will need to apply an RTRIM in your Source and/or Lookup transformation. If you are convinced RTRIM isn't working your source, add a Derived Column Transformation and then apply your RTRIM there but I find it's better to abuse the database instead of SSIS.

Biml

Biml, the Business Intelligence Markup Language, describes the platform for business intelligence. BIDS Helper, is a free add on for Visual Studio/BIDS/SSDT that we're going to use to transform a Biml file below into an SSIS package.

The following biml will generate the

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2012;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;" />
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="so_26719974">
            <Tasks>
                <Dataflow Name="DFT Demo">
                    <Transformations>
                        <OleDbSource 
                            ConnectionName="CM_OLE" 
                            Name="OLESRC Source">
                            <DirectInput>
                            SELECT
                                D.SourceColumn
                            ,   D.Description
                            FROM
                            (
                                VALUES 
                                    (CAST('T' AS varchar(4)), 'T')
                                ,   (CAST('R' AS varchar(4)), 'R')
                                ,   (CAST('' AS varchar(4)), 'Empty string')
                                ,   (CAST('    ' AS varchar(4)), 'Blanks')
                                ,   (NULL, 'NULL')
                            ) D (SourceColumn, Description);
                            </DirectInput>
                        </OleDbSource>
                        <Lookup 
                            Name="LKP POC"
                            OleDbConnectionName="CM_OLE"
                            NoMatchBehavior="RedirectRowsToNoMatchOutput"
                            >
                            <DirectInput>
                            SELECT
                                D.SourceColumn
                            FROM
                            (
                                VALUES 
                                    (CAST('T' AS varchar(4)))
                                ,   (CAST('R' AS varchar(4)))
                                ,   (CAST('' AS varchar(4)))
                            ) D (SourceColumn);
                            </DirectInput>
                            <Inputs>
                                <Column SourceColumn="SourceColumn" TargetColumn="SourceColumn"></Column>
                            </Inputs>
                        </Lookup>
                        <DerivedColumns Name="DER Default catcher" />
                        <DerivedColumns Name="DER NoMatch catcher">
                            <InputPath OutputPathName="LKP POC.NoMatch" />
                        </DerivedColumns>
                    </Transformations>

                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

这篇关于使用完全高速缓存时，查找组件无法匹配空字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！