0

My SSIS package has a ado.net source, which returns columns with width more than 2000 characters,but those columns get truncated when redirected output to excel destination.

Any idea, how to resolve this problem..?

I am using excel 2007, as excel destination.

2
  • Is your column length set to be 2000 in the data source? – Zane Aug 4 '14 at 16:30
  • Yes..Column length is varchar(2000).. – CodeTody Aug 4 '14 at 17:17
3

SSIS Excel Data Source:

Error with output column “Comments” (5691) on output “Excel Source Output” (5596).

The column status returned was:

“Text was truncated or one or more characters had no match in the target code page.”

Reason for this error:

  1. The error is due to the lengthy data in the excel.
  2. SSIS has an inbuilt logic to scan the data in the spread sheet. It will scan the first 8 rows and based on that it will written a logic to build the table's logic for the package. If your lengthy data in not in the 8 row then your ssis wont respond it.

Considering am having a column named "Name" First 8 rows is of length <255 9th row is of length > 255. While executing the package you will get the above error, its because your input value will be truncated and SSIS wont allow for that. In this case, Two types of logics can be followed,

Option1 : Its tricky one, just copy the 9th row to the top (may be as 1 row).Now try to create your package using import/export wizard. Your ssis will create column with width nvarchar(Max) which will accept upto 2 GB.

Option2: Change the Preparation SQL task query, change the data type as varchar(Max) and modify the excel source ->advanced editor and change the output columns type and length and external column type and length.

Please read the blog

1
  • Thanks....I read the blog and googled a bit.. In my problem I am using Excel template to create dynamic excel destination. So I added a dummy row of length more than 255 characters and made it hidden. – CodeTody Aug 4 '14 at 17:17
1

For those searching for this later on, the easiest way to fix this is to right click on Excel Destination --> Advanced Editor --> Component Properties (page) --> Validate External Metadata = False. Then go to Input and Output Properties page and set the External Column's length - it will now allow you to change it to the appropriate data type and length without resetting it. This solution avoids having to manipulate sampled records or change registry keys.

EDIT: also check that you have the 32-bit excel driver (Microsoft Access Database Engine 2010 Redistributable) if you are using 32-bit Excel, and on the PROJECT level (right click on the Project, not the Package, and select Properties) go to Configuration Properties --> Debugging, and change Run64BitRuntime to FALSE. If you have this use case (32-bit Excel), you will be chasing your tail for hours thinking that the problem is with data type validation.

-2

By changing destination to the Microsoft Excel 97-2003 and use the xls for file extension.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.