Ragged dest file doesn''t like truncating values
-
Hi All, I am having a problem where my SSIS package is failing when writting to a ragged flat file. I am pulling my data from a view where one of the misbehaving columns is a float and another is a varchar. Whenever the destination connection encountered a value which was longer than the specified column length then it would error out. I would like it to simple truncate the value. In the source connection I have used the left() function in a SQL select statement which previews correctly but doesn't seem to have any effect on shortening the value for the destination connection.
I have tried transfering the select statement to a second view where I do some fancy conversion to get the float to come out just the way I want it. (I never thought that converting floats to varchars correctly could be so difficult). Here is the fancy code.
left(convert(varchar(255),convert(decimal(38,20),value)),16) as value
Like I said, this looks great when I preview it, but the destination connection errors out every time with this error:
Error: 0xC02020A1 at Copied data in table ***** database data internet payments Pay txt, Connection 2 [43]: Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
I am beside myself with frustration and would greatly appreciate any help. The destination column is a string[DT_STR] of width 17; however I have also tried the unicode [DT_WSTR] to no avail.
-
Have you thought about *not* doing the conversion in sql but instead using a script component in the data transform? Like yourself, I've had lots of trouble doing this type of extract to file packages. I thought the GUI was supposed to make this easy??!!
-
I am migrating this package from DTS and, like you, I assumed that SSIS wouldn't make it more difficult that DTS.
However, that is a good suggestion. Thanks and I will try it.
-
You're not doing the conversion in SSIS (currently) so how exactly is it SSIS that is making this more difficult?
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Thanks for helping Jamie.
In DTS the package consisted of a SQL select statement with a flat file destination. By default, DTS would export as much of the values as it could fit into the fixed length row and simply chop off the extra on the right side of the value.
In SSIS when I tried to duplicate this, it would constantly throw errors saying that values were going to be truncated. When I tried to use the "Left", "Convert", and "Substring" functions in the select statement then it rounds my "float" column to about two decimal places for the preview button. Unfortunately, even when I use these functions, this doesn't seem to affect the data which it is trying to dump out to the flat file, it still throws an error trying to dump out the entire value which still needs to be truncated. If I increase the fixed width column size for those which are erroring out then it will dump out the entire value (even though my left|convert|substring statement should be truncating it).
It should be a simple two step job, but I can't figure out how to tell SSIS to ignore the truncation errors; or to get the select statement to correctly truncate the data. Going with Mike's suggestion I have successfully re-written the job using a foreach loop and a script task to manually truncate and pad every value before writting it out to a file. Unfortunately the select statement is still rounding (instead of chopping off) the float values.
Jereme
-
Then it seems as though the problem is in the source. Do you get the same behaviour if you execute the query in SSMS?
If so, I would check out the Tt-SQL forum for that is where the problem seems to lie unless I have misunderstood - which is highly probable
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
The SSIS flat file destination should allow you to ignore errors on specific columns so the package doesn't abort. Go through the property pages of your data flow components and you should find it.
But you also should be able to solve your problem in the source query. If you want the float column to be a fixed number of decimal places (say 4), then ROUND(x, 4) should do it. If you require truncation instead of rounding, use ROUND(x, 4, 1).
Another alternative would be to use STR(x, 20, 4) to specify both the column width and number of decimals. Truncation is a little more complicated, but can be accomplished with either STR( x - 0.00005, 20, 4) or STR( ROUND(x, 4, 1), 20, 4).
-
the SSIS flat file destination doesn't have the ability to ignore errors that I can see. All source connections (including flat files) have the ability to ignore errors but not destination connections as far as I can see.
The ROUND(a,b,c) keyword has no effect in the source query.
The Str(a,b) keywork stops the errors by chopping off all the decimal values rather than simply rounding or truncating to the specified width.
Str(a,b,c) produces some odd effects by: if b >= c then it has no effect on the value. if b < c then it returns '' unless the source value is 0 in which case it will return 0
-
uugghh, MS what did you do to a simple but easy to use DTS. I too was in "truncate hell". I read all sorts of forums and blogs about setting the error output. But as a previous poster here mentioned, this option is not available on the Flat File Destination. I have wasted 2 days on something I could have written myself in VB.
What I ended up doing is creating another view in SQL that did all of the converting of the columns to the size I wanted in the textfield. A pain in the $#%, but it's working.
--------------------------------
create view xxxx
as
select left(lastname,20) as lastname
...
from ... etc.
--------------------------------
Yes, now I have a bunch of extra views in SQL I would of rather not had, but... I guess ya gotta do what ya gotta do.
Now I did not have the issues of floats that another user mentioned, so this workaround may not solve his/her problem. But at least I am my ole' cheerful self and being productive instead of yelling at anyone who walks into my cubicle. 🙂
Now if someone actually has exported to a flat file where one of the fields in the table is larger than you wanted in the flat file, I would be interested in how you got it to work.
for example say you had a UserLastName field that was defined as varchar(30) but for some reason it had to be 20 characters in the flat file. Were you able to successfully export this to a flat file using only SSIS? (as opposed to Micky Mousing it like I did with an extra View)
-
You could use the Derived Column transformation to define a new column with a LEFT(s,20) expression, instead of creating extra views. You could also put a Conditional Split transform before the flat file destination to send rows with too-long fields to a different destination.
In response to Jereme, the STR function does round to the specified number of decimal places. I know the difference between rounding and truncation, but I have no idea what your definition of the "chopping" operation is if it's not one of those two (or three, if you include banker's rounding). For the purists, the STR function converts the expression to float first which may cause some inaccuracy, but it will then produce the same result as the equivalent ROUND function using a float argument. This can be demonstrated with this query, where only the second function appears to be correct:
select str(4.755, 5, 2), round(4.755, 2), round(cast(4.755 as float), 2) -
Sorry, the chopping term came from a keyword in another language.
I was trying to get the value to "truncate". However that project is long past for me so the best I can do is speculate about it, I no longer have access to the code without replicating it from scratch.
-
Have you tried to change the error handling section for the data source object? You can set the Truncation portion to ignore failure.
Right click on your data source and choose edit.
Select the Error Output section.
Highlight the entire Truncate Error column by selecting its first row and the shift clicking on the last row.
Choose Ignore Failure in the drop down box and apply.
Also, watch out for the variable name "value". it may be reserved in SSRS
-
Yes I tried setting the error condition and the truncate condition on the DataSource object but that did not seem to work.
What I simply want to do is truncate the data since I am sending this file to an old legacy system. I know there are some intermediary "transistion" type objects and I probably need to use one of those. I created the Views I mentioned above for the 7 extracts I need. It's not the most elegant solution, but at least I delivered all of the files I need.
Thanks for everyone's help. If I have some free time ** cough ** I will re-visit this and try to find a better solution.
-
One idea would be to convert the float value to a big string to ensure no matter what the value is it will fit. Then the derived column transformation to generate a new column SUBSTRING([MyColumn,1,x)
X = Maxium length of the string you want to hold on to.
I do this all the time never had a problem unless when I cast it in as a string I made I left the allowable size too short. For that reason I always use VARCHAR(255) at a minimum.
Hope this helps..Happy Holidays!
Eric
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply