Error 0xc0204016: SSIS.Pipeline … The length must be between 0 and 8000.

About once a year, I am asked to import some text files into a table in a SQL Server database.  To accomplish this, I am using the SQL Server 2008 Import and Export Wizard.  My recent struggle was with the records that had more then 8000 characters in a column.

I was seeing two types of warnings:

The Size value is invalid.
Additional information:
The entered value is out of the allowed range for the “varchar” data type.
The valid values are greater than or equal to 1 and less than or equal to 8000.(DTSWizard)
 
Varchar8000_Warning

value is out of the allowed range for the “varchar” data type

The other error was when I attempted the perform the import operation:

Could not connect source component.
Error 0xc0204016:: SSIS.Pipeline: The “Source – < file name >_csv.Outputs[Flat File Source Output].Columns[ < column name > ]”
has a length that is not valid. The length must be between 0 and 8000.
Additional information:
Exception from HRESULT: 0xc0204016(Microsoft.SqlServer.DTSPipelineWrap)
 
 
Error_0xc0204016

Error 0xc0204016

RESOLUTION:

To resolve this issue, it started on the “Choose a Data Source” screen.  After I had chosen the CSV file, in the left panel, I clicked on “Advanced”.  Then I clicked on any of the columns.   Notice that I click on the “LastAction” column.  In the right panel, the Data Type is “string [DT_STR]”, and the OutputColumnWidth is 50.   Next I Clicked on the “Suggest Type…” button.

ChooseADataSource

Choose a Data Source screen

A pop up box appears named “Suggested Column Types”.  In the “Number of rows:” text box, enter the estimate number of rows in the CSV or text file.  I had found the number of rows in my import file by previewing it in Excel.  Then I clicked the OK button.  This may take a while, depending on the number of rows.  I have gone as high as 500,000 rows, which took a couple of minutes.  This processes scans the file, and gives recommended data types with the size.

SuggestColumnTypes

Suggest Column Types Pop-Up Box

I searched each of the columns to find any column with a width greater then 8,000 characters.  In the example below, the “Last Action” column has a record with 14,409 characters in it.

Resolution03

To resolve this issue, the Data Type is going to need to be changed, because the “string [DT_STR]” can hold at max 8,000 characters.  By changing the Data Type to something like “text stream [DT_TEXT]”, allows for more then the 8,000 character limit.  The Output column width will change to zero when I chose “text stream [DT_TEXT]”.

Resolution04a

At that point, I was able to continue on with the export process.  I did have to watch the column sizes on my table being imported into.  However my import was successful.

SuccessfulImport

Categories: SQL Server

1 Comment

  • Vishwanath says:

    Ensure that column “Last Action” data loaded into destination or not, I believe that column is unchecked while mapping then rest of the column data is loaded.

    Thanks,
    Vishwanath

Leave a Reply