SSIS：将文本流DT_TEXT转换为DT_WSTR [英] SSIS : Conversion text stream DT_TEXT to DT_WSTR

问题描述

我正在SSIS中创建一个程序包，并希望将一个大列的文件转换为多列。

I am creating a package in SSIS, and want to convert a file with one large column into multiple columns.

我有一个包含多行的表，其中一行原始数据列。数据是从记事本文件复制的，并且每一行都包含用于分隔每一列的管道定界符，但是由于它是一个记事本文件，因此每一行都被复制为一个大列。我想根据其开始/结束位置将每行的每一列转换为多列。

I have a table containing several rows with a single column of raw data. The data was copied from a notepad file, and each row contains pipe delimiters to separate each column, but because it is a notepad file, each row is copied as one large column. I want to convert each column per row to multiple columns based on their start/end positions.

我尝试将SSIS派生列转换与 SUBSTRING 函数一起使用，但是数据类型自动填充为文本流[DT_TEXT] ，出现以下错误：

I tried using SSIS Derived Column Transformation with the SUBSTRING function, but the Data Type is automatically populated as text stream[DT_TEXT], and I get the following error:

[派生错误专栏[113]]；函数 SUBSTRING
不支持参数编号1的数据类型 DT_TEXT。参数的
类型不能隐式转换为该函数的兼容
类型。要执行此操作，操作数需要使用强制转换运算符显式强制转换
。

Error at [Derived Column[113]]; The function "SUBSTRING" does not support the data type "DT_TEXT" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.

[Derived Column [113]]处的错误；评估函数
的 SUBSTRING失败，错误代码为0xC0047089。

Error at [Derived Column[113]]; Evaluating function 'SUBSTRING' failed with error code 0xC0047089.

[Derived Column [113]]错误；计算表达式
SUBSTRING [RawData]，1,5）失败，错误代码为0xC00470C5。
表达式可能有错误，例如被零除，在解析时无法检测到
，或者可能存在内存不足错误。

Error at [Derived Column[113]]; Computing the expression "SUBSTRING[RawData],1,5)" failed with error code 0xC00470C5. The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.

[Derived Column [113]]错误； 派生列.Outputs [派生列
输出] .Coluns [派生列1]上的表达式
SUBSTRING [RawData]，1,5）无效

Error at [Derived Column[113]]; The expression "SUBSTRING[RawData], 1,5)" on "Derived Column.Outputs[Derived Column Output].Coluns[Derived Column 1] is not valid

[Derived Column [113]]上的错误；无法在 Derived Column.Outputs [Derived Column
Output] .Columns [Derived Column 1]上设置属性
Expression 。

Error at [Derived Column[113]]; Failed to set property "Expression" on "Derived Column.Outputs[Derived Column Output].Columns[Derived Column 1]. "

当我使用 SUBSTRING 和文件查看其他派生列转换插图时包含各个列，我注意到数据类型显示为 DT_WSTR 。

When I review other Derived Column Transformation illustrations utilizing SUBSTRING with a file containing individual columns, I notice the Data Type is shown as DT_WSTR.

我是否需要转换为该数据如果是，则如何在SSIS派生列转换中使用强制转换运算符将 DT_TEXT 数据类型显式转换为 DT_WSTR ？

Do I need to convert to this Data Type? If so, how do I explicitly cast DT_TEXT data types to DT_WSTR with a cast operator in SSIS Derived Column Transformation?

否则，我还能如何处理此转换？

Otherwise, how else could I handle this conversion?

Derived Column Name: EmployerNo
Derived Column:      Replace 'RawData'
Expression:          SUBSTRING( [RawData], 1, 5 )
Data Type:           text stream[DT_TEXT]

我希望RawData列根据其起始位置和结束位置分为8个不同的列。 >

I expect the RawData column to be split up (converted) into 8 different columns based on their start and end positions.

推荐答案

引用 SUBSTRING（SSIS表达式）文档：

返回字符表达式中从指定位置开始并具有指定长度的部分。

Returns the part of a character expression that starts at the specified position and has the specified length.

您必须将 DT_TEXT 列转换为 DT_STR / DT_WSTR ，然后再使用 Substring（）函数，可以使用脚本组件执行此操作，也可以使用类似的函数：

You have to convert DT_TEXT column to DT_STR/DT_WSTR before using Substring() function, you can do this using a Script Component, you can use a similar function:

string BlobColumnToString(BlobColumn blobColumn)
{
    if (blobColumn.IsNull)
        return string.Empty;

    var blobLength = Convert.ToInt32(blobColumn.Length);
    var blobData = blobColumn.GetBlobData(0, blobLength);
    var stringData = Encoding.Unicode.GetString(blobData);

    return stringData;
}

或者如果 DT_TEXT 长度不超过 DT_STR 长度限制，请尝试使用以下SSIS表达式：

Or if the DT_TEXT length doesn't exceed the DT_STR length limit try using the following SSIS expression:

SUBSTRING( (DT_STR,1252,4000)[RawData], 1, 5 )

这篇关于SSIS：将文本流DT_TEXT转换为DT_WSTR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！