ssis - 为什么 SSIS TOKEN 函数无法计算相邻的列分隔符？

Question

我在名为 TOKEN() 的表达式编辑器中遇到了 SQL Server Integration Services 2012 新字符串函数的问题。

这应该可以帮助您解析分隔记录。如果记录来自平面文件，您可以使用平面文件源执行此操作。在这种情况下，我正在处理以字符串形式存储在数据库 VARCHAR 字段中的旧分隔导入记录。现在需要将它们提取、处理并重新导出为分隔字符串。例如:

1^Apple^0001^01/01/2010^Anteater^A1
2^Banana^0002^03/15/2010^Bear^B2
3^Cranberry^0003^4/15/2010^Crow^C3

如果这些字符串位于名为 OldImportRecord 的列中，分隔符是一个脱字符(如图所示)，并且我们希望将第五个字段放入派生列中，我们将使用如下表达式:

TOKEN(OldImportRecord,"^",5)

这将返回Anteater、Bear、Crow等。其实我们可以为这条记录中的每个字段创建Derived Columns(注意索引是基于一个的)，根据需要更改它们，然后再构建另一个分隔的记录进行导出.

这就是问题所在。如果我们的某些数据包含一些空字符串(或呈现为空字符串的 Null)怎么办？

4^^0004^6/15/2010^Duck^D4

TOKEN() 无法计算相邻的列分隔符，这会导致列数丢失。现在它只看到五列而不是六列。我们的 TOKEN(OldImportRecord,"^",5) 返回“D4”而不是预期的“Duck”。当我们提取第四列时，我们最终试图将“Duck”放入日期列中，各种乐趣随之而来。

这是部分解决方法:

TOKEN(REPLACE(OldImportRecord,"^^","^ ^"),"^",5)

请注意，这会错过每一对分隔符，因此对于像“5^^^^Emu^E5”这样的字符串，它会失败，在 REPLACE() 之后看起来像“5^ ^^ ^Emu^E5”。列数仍然是错误的。

所以这是我的完整解决方法。这包括两个嵌套的 REPLACE 语句()、一个用于删除多余空格的 RTRIM() 和一个 DT_STR 强制转换，因为我想将结果保留在 VARCHAR 中:

(DT_STR,255,1252)RTRIM(TOKEN(REPLACE(REPLACE(OldImportRecord,"^^","^ ^"),"^^","^ ^"),"^",5))

我发布此信息以供引用，因为其他人也可能遇到此问题。

有没有人有更好的解决方法，甚至是真正的解决方案？

Answer 1

问题原因:
TOKEN SSIS 中的方法使用 strtok 的实现函数在 C++ .我在阅读本书时收集了这些信息 Microsoft® SQL Server® 2012 Integration Services .它在 页的注释中提到113 (我喜欢这本书!很多不错的信息。)。
我搜索了strtok的实现功能，我找到了以下链接。
INFO: strtok(): C Function -- Documentation Supplement - 此链接中的代码示例显示该函数确实忽略了连续的分隔符。
以下SO问题的答案指出strtok函数旨在忽略连续的分隔符。
Need to know when no data appears between two token separators using strtok()
strtok_s behaviour with consecutive delimiters
我认为TOKEN和 TOKENCOUNT函数正在按照设计工作，但是这是否是 SSIS 的行为方式可能是 Microsoft SSIS 团队的一个问题。
原始帖子 - 以上部分是更新:
我根据您的数据输入在 SSIS 2012 中创建了一个简单的包。正如您在问题中所描述的，TOKEN功能不符合预期。我同意您的看法，该功能似乎不起作用。这个帖子是 不是 对您原始问题的回答。
这是以相对简单的方式编写表达式的另一种方法。这仅在输入记录中的最后一段始终具有值(例如 A1、B2、C3 等)时才有效。
表达式可以改写为 :
该语句将输入记录作为参数，分隔符插入符号 (^) 作为第二个参数。第三个参数计算按分隔符分割时记录中的总段数。如果最后一个段中有数据，则保证有两个段。然后您可以减去 1 以获取倒数第二个片段。

(DT_STR,50,1252)TOKEN(OldImportRecord,"^",TOKENCOUNT(OldImportRecord,"^") - 1)

我创建了一个带有数据流任务的简单包。 OLE DB 源检索数据，派生的转换按照下面的屏幕截图解析和拆分数据。然后将输出插入到目标表中。您可以在最后一个屏幕截图中看到源表和目标表。目标表有两列。第一列存储倒数第二个段数据和基于分隔符的段计数(这又是不正确的)。您可以注意到最后一条记录没有获取正确的结果。如果最后一条记录没有值 8 ，则上述表达式将失败，因为该表达式的计算结果为零索引。
希望这有助于简化您的表达。
如果您没有收到其他人的来信，我建议您在 Microsoft Connect website 中记录此问题.
创建表并填充脚本 :

CREATE TABLE [dbo].[SourceTable](
    [OldImportRecord] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DestinationTable](
    [NewImportRecord] [varchar](50) NOT NULL,
    [CaretCount] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.SourceTable (OldImportRecord) VALUES 
    ('1^Apple^0001^01/01/2010^Anteater^A1'),
    ('2^Banana^0002^03/15/2010^Bear^B2'),
    ('3^Cranberry^0003^4/15/2010^Crow^C3'),
    ('4^^0004^6/15/2010^Duck^D4'),
    ('5^^^^Emu^E5'),
    ('6^^^^Geese^F6'),
    ('^^^^Pheasant^G7'),
    ('8^^^^Sparrow^');
GO

数据流任务中的派生列转换 :

源表和目标表中的数据 :