当数据库列定义为 VARCHAR(MAX) 时，如何使用 SSIS 传输数据? [英] How to transfer data using SSIS when database columns are defined as VARCHAR(MAX)?

问题描述

我在执行包时收到以下消息.

I am getting the below message while executing a package.

文本被截断或一个或多个字符在目标代码页中不匹配.

Text was truncated or one or more characters had no match in the target code page.

我正在从一个 SQL 表中获取数据，该表的字段名称为 task_teammember，数据类型为 VARCHAR(MAX).包执行在源头失败.我将列 task_teammber 类型转换为 VARCHAR(8000) ，它执行包而没有任何错误消息.但是，目标仅接收 8000 个字符，而源表中有 8000 个字符.

I am taking data from an SQL table which has a field names task_teammember with the data type VARCHAR(MAX). The package exeuction fails at the source. I type casted the column task_teammber into VARCHAR(8000) which executes the package without any error message. However, the destination receives only 8000 characters whereas there are more than 8000 characters in the source table.

当列定义为 VARCHAR(MAX) 时，如何使用 SSIS 将所有数据从源表传输到目标表?

How do I transfer all the data from source to destination tables using SSIS when the columns are defined as VARCHAR(MAX)?

推荐答案

您需要使用 SSIS 数据类型 text stream [DT_TEXT] 从 SQL Server 表中获取数据数据类型列varchar(MAX)

You need to use the SSIS datatype text stream [DT_TEXT] to fetch data from SQL Server table columns of data type varchar(MAX)

这是一个简单的示例，说明了 SSIS 如何从源自动推断数据类型.该示例使用 SQL Server 2008 R2 数据库和 SSIS 2008 R2

Here is a simple example that illustrates how SSIS automatically infers the datatypes from the source. The example uses SQL Server 2008 R2 database and SSIS 2008 R2

在 SQL Server 数据库中创建以下表来存储源文本并使用目标使用 SSIS 包插入文本.

Create the following tables in SQL Server database to store source text and use the destination to insert the text using SSIS package.

CREATE TABLE [dbo].[SourceTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [SourceText] [varchar](max) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[DestinationTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DestinationText] [varchar](max) NOT NULL
) ON [PRIMARY]

在源表中插入大长度文本.执行包之前可以看到源表中的数据超过10000个字符，目的表为空.

Insert text of large length into source table . You can see the data in the source table containing more than 10,000 characters and the destination table is empty before executing the package.

创建一个带有数据库连接管理器的 SSIS 包.将数据流任务放在控制流任务上.在数据流任务中，放置一个 OLE DB 源和 OLE DB 目标以将数据从 dbo.SourceTable 传输到 dbo.DestinationTable.这里的截图显示了包的执行状态.

Create an SSIS package with a connection manager to the database. Place a data flow task on the Control Flow task. Within the data flow task, place an OLE DB Source and OLE DB Destination to transfer data from dbo.SourceTable to dbo.DestinationTable. Here the screenshots shows the execution status of the package.

如果再次运行查询，您将看到目标表中填充了使用 SSIS 包的源表中的文本，没有任何截断错误.

If you run the query again, you will see that the destination table is populated with the text from source table using SSIS package without any truncation errors.

返回包的数据流任务选项卡并右键单击OLE DB Source，然后单击Show Advanced Editor...

Go back to the package's data flow task tab and right-click on the OLE DB Source and then click Show Advanced Editor...

在 OLE DB 源的高级编辑器 上，单击输入和输出属性选项卡.展开 External Columns 并选择 SourceText.您会注意到，SSIS 根据源表上定义的数据类型 VARCHAR(MAX) 将列数据类型设置为文本 stream [DT_TEXT].

On the Advanced Editor for OLE DB Source, click Input and Output Properties tab. Expand External Columns and select SourceText. You will notice that the SSIS set the column data type to text stream [DT_TEXT] based on the data type VARCHAR(MAX) defined on the source table.

以下是 SSIS 中 SQL Server 数据类型 VARCHAR(MAX) 和 NVARCHAR(MAX) 的映射.

Here are the mappings for SQL Server data types VARCHAR(MAX) and NVARCHAR(MAX) in SSIS.

在 MSDN 集成服务数据类型

Read more about it on MSDN Integration Services Data Types

希望有所帮助.

这篇关于当数据库列定义为 VARCHAR(MAX) 时，如何使用 SSIS 传输数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！