Finding 'Error Column' Causing Error

Good Morning,

Am I new at this so please bear with me. I searched the site and found threads on identifying the meaning of an error code and have instituted that scripting. It is telling me that I have a 'The data value cannot be converted for reasons other than sign mismatch or data overflow.' error. It identifies the colum as 5301.

I searched the threads and found one telling me I could do an advanced editor search on the lineage id to find the column being referenced. I have traced the entire process using this information and cannot find a reference lineage id of 5301. Was that thread information accurate, and if so what do I do now? If not, can someone tell me how the heck I find column 5301? I cannot load my table from a flat file because of this.

Work so far:

I have checked for integrity between column definitions and source flat file. I applied derived column changes to make the data transform to the appropriate data type/order where necessary. This part works without error. (Or seems to, there is no error output from this piece.) It is only on the final attempt to load that the process errors with these messages.

Thank you in advance to anyone who can help me.

Rog

It should be, I think, the INPUT column lineage ID of the offending component.|||

Tracing input columns I can find a lineage ID of 5103 NOT 5301. There is no reference in any of the ID fields, including the lineage, of 5301. Any other ideas?

|||Edit the .dtsx package file in your favorite text editor and look for 5301. Look around there a bit to see what column it references.|||

Well it turns out that worked. The column id and lineage ID are not related at all to each other. (See below.) It would seem to me there should be a better way to find this information out than this very cumbersome process.

<inputColumn id="5301" name="" description="" lineageId="5109"

|||

CaptainMyCaptain wrote:

Well it turns out that worked. The column id and lineage ID are not related at all to each other. (See below.) It would seem to me there should be a better way to find this information out than this very cumbersome process.

<inputColumn id="5301" name="" description="" lineageId="5109"

Right, well, there is. You should be able to find the INPUT column of 5301 in your data flow when looking at the advanced editor for any given component.

The lineage ID, 5109, in this case tells me that this column has gone through some transformations along the way, but it was originally 5109 somewhere along the line. If you look back a ways, you should find an id="5109" on column further up in the .dtsx source. That is this column's "parent."

Find floats with exponential notation

I'm having trouble identifying a column that is a float data type that uses
exponential notation. I've tried Casting and Converting to various data
types in order to find the 'E' but the 'E' never comes through. Is there a
better way to identify any float that displays with exponential notation?
TIA
Matt
The float column is stored as a binary number with a binary exponent.
It may be displayed with an E, but this is simply the way the front
end shows it. Internally there is no E.
The size at which a float is displayed using the E notation is built
into the front end program performing the display. It is not an
attribute of SQL Server.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 10:57:21 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:

>I'm having trouble identifying a column that is a float data type that uses
>exponential notation. I've tried Casting and Converting to various data
>types in order to find the 'E' but the 'E' never comes through. Is there a
>better way to identify any float that displays with exponential notation?
>TIA
>Matt
>
|||> The size at which a float is displayed using the E notation is built
> into the front end program performing the display. It is not an
> attribute of SQL Server.
I understand that, but I still need to identify those that display using exp
notation. What is the internal mechanism that causes SQL server to display
the number as exponential? In our case it is negative numbers with a decimal
and a leading 0. The issue I'm facing is that someone queried a table,
copied and pasted the results into a text file and imported the data into a
program. The columns that had exponential notation are off by 2-3 decimal
places because of that. There were probably a hundred thousand rows
extracted and the data was heavily manipulated prior to the import. There
are relatively few rows that had the data in exp notation but they have a
major effect on the dataset. It would be much easier to re-query the data
and cast it to varchar or decimal and just extract and import those rows
instead of trying to do the whole thing (which was about a weeks worth of
work) again but in order to do that, I need a way to identify the rows where
that field is exponential notation. So, if there is ANY way to determine if
a float will display using exponential notation, I'd really like to know
how. If anyone has other suggestions on how to handle this situation whether
it be SQL or otherwise, I'm open to suggestions.
TIA
Matt
|||If you use a client that displays floats in scientific notation (which
is quite common), and this notation is a problem, then you shouldn't use
float.
One way to do that, is to create a view that casts the float to a
decimal, and instruct your users / applications to use that view (and/or
disallow selects from the base table).
As mentioned by Roy, the value that is transferred to the client does
not contain an "E".
Gert-Jan
Matt Williamson wrote:
>
> I understand that, but I still need to identify those that display using exp
> notation. What is the internal mechanism that causes SQL server to display
> the number as exponential? In our case it is negative numbers with a decimal
> and a leading 0. The issue I'm facing is that someone queried a table,
> copied and pasted the results into a text file and imported the data into a
> program. The columns that had exponential notation are off by 2-3 decimal
> places because of that. There were probably a hundred thousand rows
> extracted and the data was heavily manipulated prior to the import. There
> are relatively few rows that had the data in exp notation but they have a
> major effect on the dataset. It would be much easier to re-query the data
> and cast it to varchar or decimal and just extract and import those rows
> instead of trying to do the whole thing (which was about a weeks worth of
> work) again but in order to do that, I need a way to identify the rows where
> that field is exponential notation. So, if there is ANY way to determine if
> a float will display using exponential notation, I'd really like to know
> how. If anyone has other suggestions on how to handle this situation whether
> it be SQL or otherwise, I'm open to suggestions.
> TIA
> Matt
|||There is no single answer because this is not done by SQL Server but
by whatever front-end program processes the result of the SELECT.

>The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that.
All I can suggest is find out what tool that person used, along with
whatever configuration settings were in place, and experiment until
you understand what that tool does to display exponential data. If
they repeat that very first step perhaps you can pick the problems out
there.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 13:19:47 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:

>I understand that, but I still need to identify those that display using exp
>notation. What is the internal mechanism that causes SQL server to display
>the number as exponential? In our case it is negative numbers with a decimal
>and a leading 0. The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that. There were probably a hundred thousand rows
>extracted and the data was heavily manipulated prior to the import. There
>are relatively few rows that had the data in exp notation but they have a
>major effect on the dataset. It would be much easier to re-query the data
>and cast it to varchar or decimal and just extract and import those rows
>instead of trying to do the whole thing (which was about a weeks worth of
>work) again but in order to do that, I need a way to identify the rows where
>that field is exponential notation. So, if there is ANY way to determine if
>a float will display using exponential notation, I'd really like to know
>how. If anyone has other suggestions on how to handle this situation whether
>it be SQL or otherwise, I'm open to suggestions.
>TIA
>Matt
>

Find floats with exponential notation

I'm having trouble identifying a column that is a float data type that uses
exponential notation. I've tried Casting and Converting to various data
types in order to find the 'E' but the 'E' never comes through. Is there a
better way to identify any float that displays with exponential notation?
TIA
MattThe float column is stored as a binary number with a binary exponent.
It may be displayed with an E, but this is simply the way the front
end shows it. Internally there is no E.
The size at which a float is displayed using the E notation is built
into the front end program performing the display. It is not an
attribute of SQL Server.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 10:57:21 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>I'm having trouble identifying a column that is a float data type that uses
>exponential notation. I've tried Casting and Converting to various data
>types in order to find the 'E' but the 'E' never comes through. Is there a
>better way to identify any float that displays with exponential notation?
>TIA
>Matt
>|||> The size at which a float is displayed using the E notation is built
> into the front end program performing the display. It is not an
> attribute of SQL Server.
I understand that, but I still need to identify those that display using exp
notation. What is the internal mechanism that causes SQL server to display
the number as exponential? In our case it is negative numbers with a decimal
and a leading 0. The issue I'm facing is that someone queried a table,
copied and pasted the results into a text file and imported the data into a
program. The columns that had exponential notation are off by 2-3 decimal
places because of that. There were probably a hundred thousand rows
extracted and the data was heavily manipulated prior to the import. There
are relatively few rows that had the data in exp notation but they have a
major effect on the dataset. It would be much easier to re-query the data
and cast it to varchar or decimal and just extract and import those rows
instead of trying to do the whole thing (which was about a weeks worth of
work) again but in order to do that, I need a way to identify the rows where
that field is exponential notation. So, if there is ANY way to determine if
a float will display using exponential notation, I'd really like to know
how. If anyone has other suggestions on how to handle this situation whether
it be SQL or otherwise, I'm open to suggestions.
TIA
Matt|||If you use a client that displays floats in scientific notation (which
is quite common), and this notation is a problem, then you shouldn't use
float.
One way to do that, is to create a view that casts the float to a
decimal, and instruct your users / applications to use that view (and/or
disallow selects from the base table).
As mentioned by Roy, the value that is transferred to the client does
not contain an "E".
Gert-Jan
Matt Williamson wrote:
> > The size at which a float is displayed using the E notation is built
> > into the front end program performing the display. It is not an
> > attribute of SQL Server.
> I understand that, but I still need to identify those that display using exp
> notation. What is the internal mechanism that causes SQL server to display
> the number as exponential? In our case it is negative numbers with a decimal
> and a leading 0. The issue I'm facing is that someone queried a table,
> copied and pasted the results into a text file and imported the data into a
> program. The columns that had exponential notation are off by 2-3 decimal
> places because of that. There were probably a hundred thousand rows
> extracted and the data was heavily manipulated prior to the import. There
> are relatively few rows that had the data in exp notation but they have a
> major effect on the dataset. It would be much easier to re-query the data
> and cast it to varchar or decimal and just extract and import those rows
> instead of trying to do the whole thing (which was about a weeks worth of
> work) again but in order to do that, I need a way to identify the rows where
> that field is exponential notation. So, if there is ANY way to determine if
> a float will display using exponential notation, I'd really like to know
> how. If anyone has other suggestions on how to handle this situation whether
> it be SQL or otherwise, I'm open to suggestions.
> TIA
> Matt|||There is no single answer because this is not done by SQL Server but
by whatever front-end program processes the result of the SELECT.
>The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that.
All I can suggest is find out what tool that person used, along with
whatever configuration settings were in place, and experiment until
you understand what that tool does to display exponential data. If
they repeat that very first step perhaps you can pick the problems out
there.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 13:19:47 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>> The size at which a float is displayed using the E notation is built
>> into the front end program performing the display. It is not an
>> attribute of SQL Server.
>I understand that, but I still need to identify those that display using exp
>notation. What is the internal mechanism that causes SQL server to display
>the number as exponential? In our case it is negative numbers with a decimal
>and a leading 0. The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that. There were probably a hundred thousand rows
>extracted and the data was heavily manipulated prior to the import. There
>are relatively few rows that had the data in exp notation but they have a
>major effect on the dataset. It would be much easier to re-query the data
>and cast it to varchar or decimal and just extract and import those rows
>instead of trying to do the whole thing (which was about a weeks worth of
>work) again but in order to do that, I need a way to identify the rows where
>that field is exponential notation. So, if there is ANY way to determine if
>a float will display using exponential notation, I'd really like to know
>how. If anyone has other suggestions on how to handle this situation whether
>it be SQL or otherwise, I'm open to suggestions.
>TIA
>Matt
>

Find floats with exponential notation

I'm having trouble identifying a column that is a float data type that uses
exponential notation. I've tried Casting and Converting to various data
types in order to find the 'E' but the 'E' never comes through. Is there a
better way to identify any float that displays with exponential notation?
TIA
MattThe float column is stored as a binary number with a binary exponent.
It may be displayed with an E, but this is simply the way the front
end shows it. Internally there is no E.
The size at which a float is displayed using the E notation is built
into the front end program performing the display. It is not an
attribute of SQL Server.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 10:57:21 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:

>I'm having trouble identifying a column that is a float data type that uses
>exponential notation. I've tried Casting and Converting to various data
>types in order to find the 'E' but the 'E' never comes through. Is there a
>better way to identify any float that displays with exponential notation?
>TIA
>Matt
>|||> The size at which a float is displayed using the E notation is built
> into the front end program performing the display. It is not an
> attribute of SQL Server.
I understand that, but I still need to identify those that display using exp
notation. What is the internal mechanism that causes SQL server to display
the number as exponential? In our case it is negative numbers with a decimal
and a leading 0. The issue I'm facing is that someone queried a table,
copied and pasted the results into a text file and imported the data into a
program. The columns that had exponential notation are off by 2-3 decimal
places because of that. There were probably a hundred thousand rows
extracted and the data was heavily manipulated prior to the import. There
are relatively few rows that had the data in exp notation but they have a
major effect on the dataset. It would be much easier to re-query the data
and cast it to varchar or decimal and just extract and import those rows
instead of trying to do the whole thing (which was about a weeks worth of
work) again but in order to do that, I need a way to identify the rows where
that field is exponential notation. So, if there is ANY way to determine if
a float will display using exponential notation, I'd really like to know
how. If anyone has other suggestions on how to handle this situation whether
it be SQL or otherwise, I'm open to suggestions.
TIA
Matt|||If you use a client that displays floats in scientific notation (which
is quite common), and this notation is a problem, then you shouldn't use
float.
One way to do that, is to create a view that casts the float to a
decimal, and instruct your users / applications to use that view (and/or
disallow selects from the base table).
As mentioned by Roy, the value that is transferred to the client does
not contain an "E".
Gert-Jan
Matt Williamson wrote:
>
> I understand that, but I still need to identify those that display using e
xp
> notation. What is the internal mechanism that causes SQL server to display
> the number as exponential? In our case it is negative numbers with a decim
al
> and a leading 0. The issue I'm facing is that someone queried a table,
> copied and pasted the results into a text file and imported the data into
a
> program. The columns that had exponential notation are off by 2-3 decimal
> places because of that. There were probably a hundred thousand rows
> extracted and the data was heavily manipulated prior to the import. There
> are relatively few rows that had the data in exp notation but they have a
> major effect on the dataset. It would be much easier to re-query the data
> and cast it to varchar or decimal and just extract and import those rows
> instead of trying to do the whole thing (which was about a weeks worth of
> work) again but in order to do that, I need a way to identify the rows whe
re
> that field is exponential notation. So, if there is ANY way to determine i
f
> a float will display using exponential notation, I'd really like to know
> how. If anyone has other suggestions on how to handle this situation wheth
er
> it be SQL or otherwise, I'm open to suggestions.
> TIA
> Matt|||There is no single answer because this is not done by SQL Server but
by whatever front-end program processes the result of the SELECT.

>The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that.
All I can suggest is find out what tool that person used, along with
whatever configuration settings were in place, and experiment until
you understand what that tool does to display exponential data. If
they repeat that very first step perhaps you can pick the problems out
there.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 13:19:47 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:

>I understand that, but I still need to identify those that display using ex
p
>notation. What is the internal mechanism that causes SQL server to display
>the number as exponential? In our case it is negative numbers with a decima
l
>and a leading 0. The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that. There were probably a hundred thousand rows
>extracted and the data was heavily manipulated prior to the import. There
>are relatively few rows that had the data in exp notation but they have a
>major effect on the dataset. It would be much easier to re-query the data
>and cast it to varchar or decimal and just extract and import those rows
>instead of trying to do the whole thing (which was about a weeks worth of
>work) again but in order to do that, I need a way to identify the rows wher
e
>that field is exponential notation. So, if there is ANY way to determine if
>a float will display using exponential notation, I'd really like to know
>how. If anyone has other suggestions on how to handle this situation whethe
r
>it be SQL or otherwise, I'm open to suggestions.
>TIA
>Matt
>