March 22, 2012 at 3:51 pm
In a ssis 2008 r2 package that I want to write, there is one column that I am getting truncation errors on. This is a description column that is supplied by the customer. The length of the column can be any length that excel will allow. (Excel is the way I will obtain the data).
What do you suggest I do in SSIS to avoid this truncation error? I do not think calling the column size varchar(max) or nvarchar(max) will solve the problem.
March 22, 2012 at 3:57 pm
Use a derived column and grab the left of the field.
Anything that can have over 255 characters in Excel tries to come in as memo/Varchar(Max). If you want to keep it in Varchar(x), use a LEFT in the derived column and import that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 23, 2012 at 7:59 am
what does the LEFT in the derived column and import that do? how does that help?
March 23, 2012 at 10:45 am
It will limit the size by taking the number of characters you need, dump the rest. Since the data will fit in the target, no truncation means no error.
You could otherwise set the truncation error to be ignored on the Error portion of the component itself.
March 23, 2012 at 12:04 pm
In your original statement you said "If you want to keep it in Varchar(x), use a LEFT in the derived column and import that."
Where would you have the derived column? Is there some kind of a query you would be running to get the 'derived column?
Thanks for explaining this to me!
March 23, 2012 at 12:23 pm
You add a derived column transformation to your data flow. Using the column where the data is too long create a new column with a meaningful name (I always prefix my derived columns with DER_ and then put the name of the source column) and then do a SUBSTRING(yourcolumnthatistoolong,1,255) . You can replace the number 255 with a character length that you feel is appropriate.
March 26, 2012 at 1:38 pm
Daniel Bowlin (3/23/2012)
You add a derived column transformation to your data flow. Using the column where the data is too long create a new column with a meaningful name (I always prefix my derived columns with DER_ and then put the name of the source column) and then do a SUBSTRING(yourcolumnthatistoolong,1,255) . You can replace the number 255 with a character length that you feel is appropriate.
+1
Sorry about that, I fell offline on Friday. As Daniel mentions here, just add it to the data flow. You really should probably go though an 'intro to SSIS controls' somewhere on the net that'll walk you through, at a high level, what all the available controls are in SSIS. It'll make your life a lot easier in the long run if you know what's there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply