question

Seena avatar image
Seena asked

SSIS Excel Source Component Issue Wile Excel sheet Import

In my ssis Package i am using Excel Source Component.I am executing the package from web application.When i execute the package in the error log I am getting the below error.

There was an error with output column "RiskName" (9493) on output "Excel Source Output" (9438). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

I am able to resolve this issue if i could open the package in the BID and manually go and change the column datatype matching with the ExternalMetadata column.But I could not do this each and every time since i am running the package from web application not from the BID.I am selecting the Excel file name and sheet name from the application and passing through package variable.

I have designed the package by selecting an Excel sheet.But when i select different excel sheet from outside its metadata column datatype mismatch is happening.How can i resolve this issue without reopen and edit the Excel source column in Package.

At the execution time can i access the Metadatacolumn of Excelsource component and change it?

Thanks in advance.

excelsource-control
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

This error "Text was truncated or one or more characters had no match in the target code page." can sometimes be caused by the Jet driver (used for excel data sources) only looking at the first 8 rows of the data to determine the relevant datatype. (An example of this has been mentioned here)

There is a registry change you can make to change this value, details here

As usual, take the necessary precautions BEFORE hacking the registry.

Not sure if this solves your particular scenario, but may point you in the right direction.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Seena avatar image Seena commented ·
This I have already tried.Once i got this error,i tried this opion and it fixed my issue.But the problem is i am changing the excel sheet dynamically,not form the BID.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.