How to transfer data using SSIS when database columns are defined as VARCHAR(MAX)?

Question

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.

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.

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

Answer 1

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

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

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]

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.

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.

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.

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

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.

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

VARCHAR(MAX) ---> text stream [DT_TEXT]

NVARCHAR(MAX) ---> Unicode text stream [DT_NTEXT]

Read more about it on MSDN Integration Services Data Types

Hope that helps.