Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

[Intro] I have written a two pack SSIS solution to perform a scheduled hourly update of data from multiple tables in a single db (SRC) to single table on another db (TRG).

I get the following errors and I understand them:

[ADO NET Destination [328]] Error: An exception has occurred during data insertion, the message returned from the provider is: * String or binary data would be truncated. * The statement has been terminated.

I Know that the data from SRC in some columns is too large for the data in the TRG matching column. For instance SRC.CompositeAddress is nvarchar(50) and TRG.Addr is char(6) (same size as Condensed Data Type) which does not make sense for an address

[Question] My question revolves around not really understanding the condensed data type. Aside from data in SRC being too large for data in TRG I am wondering if condensed data type is affecting my SSIS import?

share|improve this question
    
@Marian - Yep and I just found out what the true issue was: poor database design. Turns out even though the SRC and TRG are called address fields the TRG is actually some Stored Procedure generated char(6) key to a normalized table - it tripped me up because I normally would use an identity field to and a better name to signify a FK relationship –  Tab Apr 3 '13 at 17:28
    
You have to be joking. You're generating unique char keys? :) Make sure that when you match the data types, you have them both either NVARCHAR or VARCHAR. Binary truncation can also happen from converting a nvarchar(30) to a varchar(30) (storage for national chars is double than for simple char). –  Marian Apr 3 '13 at 17:34
    
Well, I'm not as this is an existing database 'design' but yes, that is what is going on - now I have to change my entire import side to accommodate. I do not believe I will be doing any converting at this point between the those values I originally described but will most likely be writing a ton more logic to solve the issue. sigh... –  Tab Apr 3 '13 at 17:52

1 Answer 1

up vote 1 down vote accepted

AFAIK a condensed type is only an alias used in Management Studio, it's nothing else then the definition of the Type including length information (instead of having two fields: Type and Length - Varchar and 60, you have only one = Varchar(60)).

Now, that error of yours happens obviously because of data truncation. In order for it to pass, you need the original and the source to be of the same type and length (either NVARCHAR(50) or CHAR(6)). For the length it's more important that the source is smaller than the destination.

So you need to pick your necessary type (according to the input data or the destination) and make them both similar. If the input data needs a cleanup, then you better prepare it before inserting it.

share|improve this answer
    
Thanks - your info is helpful! –  Tab Apr 3 '13 at 17:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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