looping through report items of a rendered report

Hi all,
Situation: I have a WinForm application that uses the SQL 2005 reportviewer
component. That reportviewer uses a local report. The report definition
contains a report item (textbox) called "txtEmail" in the table header.
Would it be possible to walk (in runtime!) throught all the report items of
a rendered report so I can get the value of the report item "txtEmail"? I've
tried this code to walk through all the items in a rendered report:
ListControls(reportViewer.Controls);
private static void ListControls(Control.ControlCollection control)
{
foreach (Control e in control)
{
Console.WriteLine(" * " + e.ToString());
ListControls(e.Controls);
}
}
but it didn't list the actual report items. I expected to see some output
like " * txtEmail" but that control isn't listed.
Purpose: I have a report that lists a single email address in the table
header. I have overridden the
default export behaviour of the reportviewer so I can render the report to
PDF and mail it to a emailaddress that is available in the rendered report.
Any suggestions of how to accomplish this'
I am using VS2005 with local report attached to a WinForm reportviewer
component.
Kind regards,
PeterIn the past I passed a reference of ReportItems to my custom assembly, then
would walk through and grab the values I needed. For my purposes, turned
out there was a more efficient method with hidden textboxes. Also I had
some pages with static text and no rows. Inspecting the ReportItems
collection on these pages would throw an internal error and the report would
fail. Anyway, here's the code I used:
static string _SchoolName;
public string
SchoolName(Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItems
input)
{
string ReturnVal = null;
try
{
Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem
rptItemSchoolNameHidden = null;
rptItemSchoolNameHidden = input["txtSchoolNameHidden"];
if (rptItemSchoolNameHidden != null)
{
if (rptItemSchoolNameHidden.Value.ToString().Length > 0)
{
_SchoolName =rptItemSchoolNameHidden.Value.ToString();
}
}
}
catch
{
//ignore report item not found error.
}
return _SchoolName;
}
Steve MunLeeuw
"Peter Bons" <joepie@.blakjsd.bl> wrote in message
news:uhlMDzS6GHA.4592@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> Situation: I have a WinForm application that uses the SQL 2005
> reportviewer
> component. That reportviewer uses a local report. The report definition
> contains a report item (textbox) called "txtEmail" in the table header.
> Would it be possible to walk (in runtime!) throught all the report items
> of a rendered report so I can get the value of the report item "txtEmail"?
> I've tried this code to walk through all the items in a rendered report:
> ListControls(reportViewer.Controls);
> private static void ListControls(Control.ControlCollection control)
> {
> foreach (Control e in control)
> {
> Console.WriteLine(" * " + e.ToString());
> ListControls(e.Controls);
> }
> }
> but it didn't list the actual report items. I expected to see some output
> like " * txtEmail" but that control isn't listed.
> Purpose: I have a report that lists a single email address in the table
> header. I have overridden the
> default export behaviour of the reportviewer so I can render the report to
> PDF and mail it to a emailaddress that is available in the rendered
> report.
> Any suggestions of how to accomplish this'
> I am using VS2005 with local report attached to a WinForm reportviewer
> component.
> Kind regards,
> Peter
>
>|||Hi!
Just checking if you managed to solve the problem. I actually wanted to
ask if I could have an embedded code function instead of an external
assembly where in I could access the ReportItems collection for given
"textbox" and loop through all the values.
Thanks in advance for any suggestions.
Steve MunLeeuw wrote:
> In the past I passed a reference of ReportItems to my custom assembly, then
> would walk through and grab the values I needed. For my purposes, turned
> out there was a more efficient method with hidden textboxes. Also I had
> some pages with static text and no rows. Inspecting the ReportItems
> collection on these pages would throw an internal error and the report would
> fail. Anyway, here's the code I used:
> static string _SchoolName;
> public string
> SchoolName(Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItems
> input)
> {
> string ReturnVal = null;
> try
> {
> Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem
> rptItemSchoolNameHidden = null;
> rptItemSchoolNameHidden = input["txtSchoolNameHidden"];
> if (rptItemSchoolNameHidden != null)
> {
> if (rptItemSchoolNameHidden.Value.ToString().Length > 0)
> {
> _SchoolName => rptItemSchoolNameHidden.Value.ToString();
> }
> }
> }
> catch
> {
> //ignore report item not found error.
> }
> return _SchoolName;
> }
> Steve MunLeeuw
>
> "Peter Bons" <joepie@.blakjsd.bl> wrote in message
> news:uhlMDzS6GHA.4592@.TK2MSFTNGP04.phx.gbl...
> > Hi all,
> >
> > Situation: I have a WinForm application that uses the SQL 2005
> > reportviewer
> > component. That reportviewer uses a local report. The report definition
> > contains a report item (textbox) called "txtEmail" in the table header.
> > Would it be possible to walk (in runtime!) throught all the report items
> > of a rendered report so I can get the value of the report item "txtEmail"?
> > I've tried this code to walk through all the items in a rendered report:
> >
> > ListControls(reportViewer.Controls);
> >
> > private static void ListControls(Control.ControlCollection control)
> >
> > {
> >
> > foreach (Control e in control)
> >
> > {
> >
> > Console.WriteLine(" * " + e.ToString());
> >
> > ListControls(e.Controls);
> >
> > }
> >
> > }
> >
> > but it didn't list the actual report items. I expected to see some output
> > like " * txtEmail" but that control isn't listed.
> >
> > Purpose: I have a report that lists a single email address in the table
> > header. I have overridden the
> > default export behaviour of the reportviewer so I can render the report to
> > PDF and mail it to a emailaddress that is available in the rendered
> > report.
> >
> > Any suggestions of how to accomplish this'
> > I am using VS2005 with local report attached to a WinForm reportviewer
> > component.
> >
> > Kind regards,
> > Peter
> >
> >
> >

Lookup with multiple hits

In a Lookup component I've defined a SQL query which returns a sorted resultset. For each Lookup component input row I want to have a single output row. Problem is that for each input row there is possibility of multiple matches in SQL query resultset. From all of the possible multiple hits I want only the first one to be returned, and if no match is found then no output row. How to implement this?

Try changing your lookup query to only return the rows you are interesting in.

Code Block

select

colA

,colB

,max or min (colC)

from

tableA

group by

colA

,colB

|||

The lookup component does this by default does it not?

Edit: that is return only the first result that it runs into. Just make sure that you return the correct row first and you should be fine...

Try the following to prove:

select 19000101 as datekey, '01/01/1900' as datename

UNION

select 19000102 as datekey, '01/02/1900' as datename

UNION

select 19000103 as datekey, '01/03/1900' as datename

UNION

select 19000104 as datekey, '01/04/1900' as datename

UNION

select 19000105 as datekey, '01/05/1900' as datename

in an oledb source

and then in the lookup

select 19000101 as datekey, 'myname11' as name

UNION

select 19000102 as datekey, 'myname21' as name

UNION

select 19000103 as datekey, 'myname31' as name

UNION

select 19000104 as datekey, 'myname41' as name

UNION

select 19000101 as datekey, 'myname12' as name

UNION

select 19000102 as datekey, 'myname22' as name

UNION

select 19000102 as datekey, 'myname23' as name

Notice, there is no match for 19000105, it will redirect.

You will return the values

19000101, '01/01/1900', 'myname11'

19000102, '01/02/1900', 'myname21'

19000103, '01/03/1900', 'myname31'

19000104, '01/04/1900', 'myname41'

|||

Eric Wisdahl wrote:

The lookup component does this by default does it not?

I believe so. The message about duplicate values is just a warning, not an error. I don't think there's a guaranteed order -- it just picks the first one it comes across.|||

Eric Wisdahl wrote:

The lookup component does this by default does it not?

Yes. Lookup is a synchronous component; hence the number of rows in the output is the same than the number of rows in the input. If your lookup query returns more than one row for an incoming row; then 'the first' one would be used; and you don't have control over which one would be used.|||SQL query returns result set of multiple contact persons for multiple companies. On output I need for each company to filter out just one of the contact persons. Result set is sorted (ORDER BY) so that if many contact persons are found per comapny one contact person that should be chosen as highest in order. On input of the Lookup component I've put OLE_SRC component which fetches all the companies.

I couldn't use just GROUP BY because I need columns in resultset which aren't used in aggregate function nor should be be group on.
|||

I would not feel comfortable relying in SSIS picking the 1st from the list. If the query in the lookup component is against SQL Server, Oracle or other RDBS where the rank function I would use a query like:

http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html

Notice that you could mimic the result set even without the rank() function; but the query could get little complex|||Great, thank you all, especially to Rafael.

In an OLE_SRC I manually entered SQL command. I had to do it manually because it seems that Query Builder doesn't (yet) support OVER construct - when I click Build Query on OLE_SRC component it says "The OVER SQL construct or statement is not supported.".
|||

Is your source sql server 2005?

If not, you'll have to use another technique to get the row number

Code Block

select

colA

,(

select count(*)

from tableA b

where b.colA <= a.colA

) as RowNum

from tableA a

|||Yes, the source is SQL Server 2005. OLE DB Source component has Query Builder GUI which doesn't support OVER construct. I entered query manually and it works.

Lookup problem.

I'm having a problem which seems simple enough, although I can't pinpoint the failure. The problem is that I have a lookup component that always fails for all rows if caching is enabled, but succeeds for all rows if caching is disabled (for peformance reasons I need to have caching enabled). The dataflow to which the lookup component belongs to references the table exactly once - in the failing lookup component, and the containing package does not have any other references to the table in question. I have tried to use "Use Results of an SQL query" with the same results. Any suggestions?

Thanks in advance

The cached Lookups are case sensitivite, while non-cached Lookups use DB collation rules to match data, this may cause different behavior.

A common workaround is to force case of the data in caching SQL statement and in the incoming data (e.g. make both upper case).

(update)

See also another recent thread on same issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2105670&SiteID=1

|||

Thanks Michael for your help. I can get the lookup component to succeed if on the Advanced tab, I check "Enable memory restriction" and check "Modify Caching SQL Statement". However, I didn't actually modify the SQL statement, I just re-used the existing one.

To debug this, I've redirected rows which fail (when full caching is enabled) to a text file. The field used for matching is a Unicode string and has the same case in both the database table and the within the SSIS data stream (in fact, they come from the same XML input file - an earlier package populates the tables, and this package retrieves the key for the inserted values).

I hope to understand this failure before I enable the workaround. Is there anything else I could try to debug this?

|||

Some more investigating revealed that this problem is related to the length of the strings being compared. Currently the table in the database has the field declared as nvarchar(10). However, in my testcase which fails, all strings have a length of 7. If I modify the table to use nvarchar(7) for the field being compared, the Lookup component with full caching succeeds.

When the field is defined as nvarchar(10) in the table, SSIS inserts the strings without any whitespace, that is the strings are stored in the table with a length of 7. This must mean that the lookup component is comparing the string plus padding. Is there way to eliminate this behaviour? I tried using a derived column before the lookup which calls TRIM on the field to compare, but it seems that a fixed number is required for the field's length.

Thanks!

|||Try putting LTRIM(RTRIM(col)) in the lookup's source sql, and trim the value coming into the lookup as well.

Lookup problem.

I'm having a problem which seems simple enough, although I can't pinpoint the failure. The problem is that I have a lookup component that always fails for all rows if caching is enabled, but succeeds for all rows if caching is disabled (for peformance reasons I need to have caching enabled). The dataflow to which the lookup component belongs to references the table exactly once - in the failing lookup component, and the containing package does not have any other references to the table in question. I have tried to use "Use Results of an SQL query" with the same results. Any suggestions?

Thanks in advance

The cached Lookups are case sensitivite, while non-cached Lookups use DB collation rules to match data, this may cause different behavior.

A common workaround is to force case of the data in caching SQL statement and in the incoming data (e.g. make both upper case).

(update)

See also another recent thread on same issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2105670&SiteID=1

|||

Thanks Michael for your help. I can get the lookup component to succeed if on the Advanced tab, I check "Enable memory restriction" and check "Modify Caching SQL Statement". However, I didn't actually modify the SQL statement, I just re-used the existing one.

To debug this, I've redirected rows which fail (when full caching is enabled) to a text file. The field used for matching is a Unicode string and has the same case in both the database table and the within the SSIS data stream (in fact, they come from the same XML input file - an earlier package populates the tables, and this package retrieves the key for the inserted values).

I hope to understand this failure before I enable the workaround. Is there anything else I could try to debug this?

|||

Some more investigating revealed that this problem is related to the length of the strings being compared. Currently the table in the database has the field declared as nvarchar(10). However, in my testcase which fails, all strings have a length of 7. If I modify the table to use nvarchar(7) for the field being compared, the Lookup component with full caching succeeds.

When the field is defined as nvarchar(10) in the table, SSIS inserts the strings without any whitespace, that is the strings are stored in the table with a length of 7. This must mean that the lookup component is comparing the string plus padding. Is there way to eliminate this behaviour? I tried using a derived column before the lookup which calls TRIM on the field to compare, but it seems that a fixed number is required for the field's length.

Thanks!

|||Try putting LTRIM(RTRIM(col)) in the lookup's source sql, and trim the value coming into the lookup as well.

Lookup Error handling in SSIS

Hi,

I am new to using SSIS. I need to know how can I retrieve the records in a Lookup component that cause an error to use them in a Data Transfer task. I created the error event handler but I don't know how to retrieve the records causing the error to use them in the Data Transfer task.

Thanks in advance for help!

Thanks,

Aref

I tried this and it worked,

I will redirect the record in the error configuration to be the data source to the data destination.

Thanks,

Are

|||Just to be clear for anyone not sure of this, the error flow is not always nasty errors, it can be good stuff to. Think of error as the non-default condition perhaps. For a lookup, error rows are those that the lookup failed to match, so often when loading a table that has rows in already, you would the Redirect option to send "new" rows down the error output, and then insert them.

Lookup Component Stalls

i am using a lookup component to do a typical SCD. Compare the Natural keys and if they are the same -- REdirect the rows and do whatever, If not present -- means the Error Rows -- redirect and do whatever.

WHen I use the component to do a Historical Load (which means -- there are no rows are in the Destination table) and put the Memory to Partial Cache -- the Data Flow STalls after about 46,000 rows, it just doesnt complete after that. But the moment I switch it to Full Cache -- it flows -- But Partial is what I am supposed to be using -- keeping in mind -- the Incremental Loads. Why does the component stall ?

I had used Partial Cache in an earlier project -- with a 18 Million Row Table --(albeit for incremental load) and it worked fine (though is was slow -- but tleast it worked) -- but now I am trying to load just 300,000 rows but it stalls.

I am using a 2GB RAM machine -- and set the Memory to 750 MB/500 MB nothing worked

I tried two different machines -- same thing happened.

Any insight will be appreciated.

I am jut wondering why you said that the partial cache is required in the historical load. I always use (after making sure the server have enough memory available) full cache.

How is your LKup set up? you should be using the only required columns; you said this is for a SCD; so you should be uisng a query with the busines/natural key columns only (please provide the number of columns and data type of the required columns). Also, how is the source component set up? are you using 'fast load'?

|||

Hmmm so you use Full Cache always? If I leave it at Full Cache -- from next time onwards during Incremental Loads -- it will load all 290,000 rows into memory and possibly less than 10 rows have changed. I dont want to do that.

Yes I bring ONLY the required columns actually 4 columns NaturalKey, InferredMember, Hash1 and Hash2 -- all Integers

Yes I use Fast Load.

I use OLEDB for my Source.

|||

JaguarRDA wrote:

Hmmm so you use Full Cache always? If I leave it at Full Cache -- from next time onwards during Incremental Loads -- it will load all 290,000 rows into memory and possibly less than 10 rows have changed. I dont want to do that.

Yes I bring ONLY the required columns actually 4 columns NaturalKey, InferredMember, Hash1 and Hash2 -- all Integers

Yes I use Fast Load.

I use OLEDB for my Source.

An INT32 field, though (call it "natural key") will only take up 1.1MB of memory to store 290,000 rows. Not too bad, eh?|||

That is exactly my point. Even with 4 columns SSIS should be able to cache the whole result set in no time. I would recommned you to watch the progress tab in BIDS to check how long the lookup caching takes. Check also the task manager to see if there is any other process taking to much resources from the box. You can try to replace the OLE DB destination with a rowcount transformation just to test the transformation speed. You can remove the lookup and destination and use the same row count to mesaure the 'reading' speed from the source component.

Make sure you are using fast load in your OLE DB destination.

Lookup component question

Hi,

i am doing a lookup to insert new records when the lookup has failed.

this works perfectly normally. however when my recordset has a name-column of type string with width 5 and my lookup-table has a name-column of char(20) the lookup will always fail and henc always inserting new records although the name "foo" should match.

is there a workaround for this, or do the compare-columns always have to be of the same type/length ?

I would say it is always a good practice to have matching datatypes. I was not aware that lookup transform treated this situation as error; but it does not surprise me.

You can add a Data conversion before the lookup to assure matching data types

Lookup Component Feature

Has anyone seen this strange behaviour?

I have a package which loads the fact data from Stage into Warehouse database. This packages normally handles early arriving facts. In that package I use lookup to check the dims which exists, and where they don't I populate the dimension and use the surrogate key to load the facts. This works fine.

I had a request to load 7 years worth of historical data. Instead of re-writing the package I took the package which handles early arriving facts and deleted the section which handles early arriving facts. I knew all the dimensions already exists and I don’t want to hinder the performance when I load millions of rows. During testing I found something very interesting.

If you have configured error path in the lookup component and removed the error path later, the package will NOT fail (won't produce error) even if the lookup can't find matching values.

Correct Behaviour Example 1:
[1] Stage fact table has 2 records, with product code 1 and 2.
[2] Warehouse Product table has only product code 1.
[3] Source - Lookup - Destination in the data flow task. Error port on lookup is not configured.
[4] From source we read 2 records, and the package will fail at lookup as it can't find Product Code 2.

Correct Behaviour Example 2:
[1] Stage fact table has 2 records, with product code 1 and 2.
[2] Warehouse Product table has only product code 1.
[3] Source - Lookup - Destination in the data flow task. Error port on lookup is configured to go to RowCount.
[4] From source we read 2 records, and the package will run successfully. It will put one record into warehouse table and send the invalid record into RowCount.

Incorrect Behaviour Example 3:
[1] Stage fact table has 2 records, with product code 1 and 2.
[2] Warehouse Product table has only product code 1.
[3] Source - Lookup - Destination in the data flow task. Delete the configured error port from lookup.
[4] From source we read 2 records, and the package will run successfully. It will put one record into warehouse table and discard the other.

My understanding if the error port is NOT configured as shown in example 2, it should fail as shown in example 1.

Am I missing a point or is this suppose to be a correct behaviour or is it a bug?

Thanks

Sutha

If you want Lookup to fail, set error disposition to Fail Component, instead of redirecting the row to error output - the error disposition controls the component fail/not-fail behavior.

If nothing is connected to error path, the redirected rows are lost, but this is not signalled as failure - there are legitimate reasons to have packages where you don't care about such rows, and want to continue package without signalling an error.

LOOKUP component does not have AlwaysUseDefaultCodePage property

Is there any reason for this?
It doesn't make sense to me. After all, we're hitting OLE DB sources with it - if the OLE DB Source component has this property, why not LOOKUP component?

-JamieHi Jamie,

The problem here is that this particular property was added very late to the OLEDB source, furthermore, the lookup component handles its sql statement quite differently from the oledb source so it wasn't trivial to add it (not that it was trivial for the oledb source either its just that the oledb source was written in a way that was considerably more straightforward to add it than for the lookup).

HTH,
Matt|||OK Matt, thanks.

I've raised something via the product feedback centre about this for the future.

-Jamie