posted 3/23/2012 by BradSchacht - Views: [4627]
In an SSIS package you have the option to choose from a wide range of source from Flat Files to writing queries using OLEDB sources such as SQL Server. Often times, such as writing queries against SQL Server, the metadata will come from the table and you won't need to worry about truncation in the source. Flat files are scanned and metadata is chosen based on the file so those are usually good to go as well. Excel however tends to just use the default length on all columns of 255 characters. This isn't problematic until you get text that exceeds that limit at which point truncation errors occur such this:
[Admin Comments [78]] Error: There was an error with output column "Admin Comments" (92) on output "Excel Source Output" (86). The column status returned was: "Text was truncated or one or more characters had no match in the target code page."
The size of the column needs to be increased for the data to be able to be passed into the data flow. A data conversion transform will not be useful in this situation as the package will fail before it gets there. Take a look below for the problem and the solution.
Situation:
We have an Excel worksheet with a column called Admin Comments and in this case I have one entry in the sheet with the following text:
"This is a text box that has more characters in it than the size of the column in SSIS. The default for the column width is 255 characters. This text exceeds that limit. Therefore when the SSIS package picks up this file it will fail if you do not change the column width."
This exceeds the 255 character default and will cause my SSIS package to fail as seen below.
I will set up my package with a data flow that is just the Excel Source and a dummy destination.
When I run the package the first time I am disappointed because of the following situation. Errors!!!!
The error messages in that nifty screenshot above that may or may not be too small to read are the following in order of appearance. Because SSIS can't just give me one useful error message. It has to give two somewhat useful and one useless in this case, it's a feature called "Keeping you busy troubleshooting so you can stay employed"
Error Message 1: [Admin Comments [78]] Error: There was an error with output column "Admin Comments" (92) on output "Excel Source Output" (86). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
Error Message 2: [Admin Comments [78]] Error: The "output column "Admin Comments" (92)" failed because truncation occurred, and the truncation row disposition on "output column "Admin Comments" (92)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error Message 3: [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Admin Comments" (78) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Why This Happens:
Part of showing the fix for this is also showing you why this error occurred. As I mentioned my text was above the 255 characters that the default generally is set to, it has somewhere around 278 characters. Right click on the Excel Source in the Data Flow and select Show Advanced Editor.
Under the Input and Output Properties tab you will notice a section for Excel Source Output. Expand that then expand Output Columns. Here a list of columns in your excel worksheet will be displayed. Select the column in question, in this case the Admin Comments column, and notice the Data Type Properties section in the pane on the right side.
You will notice that the Length is set to the default of 255 and a Unicode string. Just above that is a property called TruncationRowDisposition and is set to RD_FailComponent. This means that if the text exceeds the 255 characters allowed it will fail the component. Now to talk about solutions.
Solution 1 (The Recommended Solution): There are two solutions for the particular situation that we will discuss. The first is to simply change the Length in the Advance Properties to a value that allows all the text in the field to be brought back. Since my widest column is 278 characters I will set mine to 300 because I know that is the maximum in my source system so the field can't have more data than that. Simply change the value to 300 as shown below.
Now when you refresh the metadata for the other components in the package and run it everything works wonderfully.
Easy fix! Just change the column length in Advanced Editor for the Excel Source and you are good to go!
Solution 2 (Not so recommended):
The other option is to change the TruncationRowDisposition property from RD_FailComponent to RD_IgnoreFailure. In this case if the row exceeds the length setting the data will still be brought in, but only up to the length. So if the setting is at 255 and there are 300 characters only the first 255 are passed in. This is not recommended for obvious reasons since you loose data, but sometime that is acceptable.