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)
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
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.
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.
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.
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]”.
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.
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