0

[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?

3
  • @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, 2013 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, 2013 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, 2013 at 17:52

1 Answer 1

1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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