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."

Final Part - Min() Function

I have the final part of my report to complete. Yesterday I posted the most confusing set of threads ever on a public forum. So to cut the confusion, I am starting this thread again as now I know exactly what I need.

I have two tables each of which have a function that gathers data from them based around a set of parameters. This is then manipulated by a third function and the results of which are passed into a Stored Procedure. Here is the T-SQL syntax used for the SP;

SELECT fnWTRalldataReport.Areacode, fnWTRalldataReport.siteref, fnWTRalldataReport.estatename, fnWTRalldataReport.Securitised,

fnWTRalldataReport.unitref, fnWTRalldataReport.unittype, fnWTRalldataReport.unittype_count, fnWTRalldataReport.tenantname,

fnWTRalldataReport.tenantstatus, fnWTRalldataReport.tenantstatus_count, fnWTRalldataReport.unitstatus, fnWTRalldataReport.unitstatus_count,

fnWTRalldataReport.floortotal, fnWTRalldataReport.floortotocc, fnWTRalldataReport.floorspaceperc, fnWTRalldataReport.initialvacarea,

fnWTRalldataReport.initialvacnet, fnWTRalldataReport.TotalRent, fnWTRalldataReport.NetRent, fnWTRalldataReport.FinalRtLsincSC,

fnWTRalldataReport.rentrolldiscperc, fnWTRalldataReport.netrentpersqft, fnWTRalldataReport.ErvTot, fnWTRalldataReport.tenancyterm,

fnWTRalldataReport.landact, fnWTRalldataReport.datadate, fnWTRalldataReport.div_mgr, fnWTRalldataReport.portfolio_mgr,

fnWTRalldataReport.propcat, fnWTRalldataReport.budgeted_net_rent, fnWTRalldataReport.budgeted_occupancy,

fnWTRbudgetdata_1.budgeted_net_rent AS budget_rent, fnWTRbudgetdata_1.budgeted_occupancy AS budget_occ

FROM dbo.fnWTRalldataReport(@.dt_src_date, @.chr_div, @.vch_portfolio_no, @.vch_prop_cat) AS fnWTRalldataReport LEFT OUTER JOIN

dbo.fnWTRbudgetdata(@.dt_src_date) AS fnWTRbudgetdata_1 ON fnWTRalldataReport.siteref = fnWTRbudgetdata_1.site_ref

The result of this SQL places a value for budget_rent and budget_occ against every row that the 1st function's result set. What I want to achieve is that where the site_ref is equal in both functions results, I want it to place the budget_rent & budget_occ value against the first row only of each site_ref only.

To explain briefly the structure. Table one has various fields including site_ref and unit_ref. There are many unit_ref's per site_ref in this table. Table 2 has only site_ref and budget info. Someone yesterday suggested that I could achieve this my using something along the lines of the Min() function e.g. Min(unit_ref).

Could someone please elaborate on this for me. I have gone through my SQL book and read about min() and also BOL, but I can't quite work the syntax out to put the budget info against only one line per site based around the lowest unit_ref per site_ref.

This might seem confusing, but it is easier to read than the other thread I assure you.

Regards

If this is just for display, then I would suggest you just let the UI sort through this.

If not, you could try using temp tables to hold the results of the function calls:

select *, cast(0 as bit) as maxUnitRef
into #FNWTRALLDATAREPORT
from dbo.FNWTRALLDATAREPORT(parms)

... same with the other, no added column though

Then

UPDATE #FNWTRALLDATAREPORT
SET maxUnitRef = 1
WHERE unitRef = (select max(unitRef)
from #FNWTRALLDATAREPORT as f2
where f2.siteRef = #FNWTRALLDATAREPORT.siteRef)

Then in the final output just:

case when maxUnitRef = 1 then #FNWTRALLDATAREPORT.BUDGETED_NET_RENT else null end as BUDGETED_NET_RENT..

Does this make any sense? You might also be able to fabricate the maxUnitRef column in the function. Either way it is far easier to do this in the UI, instead of using SQL.

|||

Thank you for this, I will sort through it now. Yes in answer to your question, it is purely for display purposes only on the report.

Regards