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

Question

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.