June 3, 2015 at 3:20 pm
I am creating a package that performs a simple insert into a couple of float columns. The reason for using float is because we are storing exponents. I have four rows of raw test data and one is inserted no problem but the other one keeps failing and I can't figure out what I am doing wrong.
So the following exponent value works..
1.79E-308
But the following don't work..
1.79E+308
-1.79E308
1.79E308
Can anyone tell me why the other types of exponents aren't able to be inserted into SQL Server?
June 3, 2015 at 3:25 pm
cstg85 (6/3/2015)
I am creating a package that performs a simple insert into a couple of float columns. The reason for using float is because we are storing exponents. I have four rows of raw test data and one is inserted no problem but the other one keeps failing and I can't figure out what I am doing wrong.So the following exponent value works..
1.79E-308
But the following don't work..
1.79E+308
-1.79E308
1.79E308
Can anyone tell me why the other types of exponents aren't able to be inserted into SQL Server?
This looks a lot like testing the boundaries of the float datatypes as those exact values are the upper and lower boundaries of float. Do you really need over 300 decimal places of precision???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 3, 2015 at 3:34 pm
What error message are you getting?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 3, 2015 at 6:47 pm
This looks a lot like testing the boundaries of the float datatypes as those exact values are the upper and lower boundaries of float. Do you really need over 300 decimal places of precision???
Sean, I really think this is the work of an engineer on google.com, I asked them to clarify their needs.
What error message are you getting?
Alvin, I am getting the following error messages.
[Flat File Source [2]] Error: Data conversion failed. The data conversion for column "sa1" returned status value 6 and status text "Conversion failed because the data value overflowed the specified type.".
[Flat File Source [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Flat File Source.Outputs[Flat File Source Output].Columns[sa1]" failed because error code 0xC0209087 occurred, and the error row disposition on "Flat File Source.Outputs[Flat File Source Output].Columns[sa1]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Thank you for your assistance.
June 3, 2015 at 7:19 pm
There are basically two ranges for float, one if the number of bits for the mantissa is set to a number between 1 and 24 inclusive (treated as 24), and another if set to a value between 25 and 53 inclusive (treated as 53). See https://msdn.microsoft.com/en-us/library/ms173773.aspx
Most likely the target column got set to a float with n<=24, so the others are all out of range. The one that you say works would work because it is not outside the range.
You can confirm this by trying to set each of those as the value of a variable declared as float. If you don't specify n, so that it uses the default 53, then all those values work.
If you specify n<=24 (or use the type synonymous with float(24), real), then all will fail but the first one.
So, most likely it's not the exponent that is the issue (because those all work with a float(53)); it's the datatype of that column. That's my hunch. I hope it helps!
Cheers!
June 4, 2015 at 6:43 am
Jacob, thank you for the explanation. I reviewed the table structure and I in fact never set a value for (n) so all of the values should work no? In SQL 2012 do I have to specify float(53)? Thanks!
June 4, 2015 at 7:58 am
In that case the inserts should all work, at least on the database side. To completely rule out the target column, are you able to successfully insert those values directly into the table?
If so, then it's probably something on the SSIS side.
Cheers!
June 4, 2015 at 11:25 am
So I am a mess here, I can insert the data into the columns but SQL Server is still throwing the same errors. Is there something in SSIS I am missing? I have checked everything from the flat file source to the database column name mapping... still can't seem to figure it out.. Thanks!
The following worked when inserted into the table using SSMS.
-1.79E+308
1.79E+308
June 4, 2015 at 12:53 pm
Ok, well, that's at least progress. We know it's not the target column in the database.
To continue systematically eliminating things, have we definitely confirmed that those are indeed the values causing the errors, and that there aren't other values in the flat file that go beyond what the datatype for that column allows?
While we're on that, what is the data type specified for the sa1 column for that flat file source (right-click the flat file source->Show Advanced Editor->Input and Output Properties->Flat File Source Output->Output Columns)? It could be that it's set to something other than [DT_R8] (double precision float). If, for example, it's set to float [DT_R4], that would explain the problem.
Cheers!
June 4, 2015 at 1:44 pm
[Quote]It could be that it's set to something other than [DT_R8] (double precision float). If, for example, it's set to float [DT_R4], that would explain the problem.[/Quote]
Thank you so much, I actually had those flat file source data types set to float [DT_R4].. I changed them to a double precision float [DT_R8] and everything worked. Thank you for all your help with this, I try to stay away from the float/real data types so I didn't have any SSIS experience with them. This has made my day, thank you.
June 4, 2015 at 2:06 pm
No problem! I'm glad we were able to get to the bottom of it.
Cheers!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply