Monday, September 30, 2013

Use Recordset Destination as input in a Foreach Loop Container

The Foreach Loop containter can use ADO recordsets as enumerator. The ADO recordset is stored in a variable of type ‘Object’. Often the ADO recordset is populated with an Excecute SQL Task. But when the data that needs to be enumerated in the Foreach Loop container must be found by combining 2 databases on different servers, the use of an Excecute SQL Task is less obvious.. A solution is to create a DataFlow that populates a Recordset Destination. This Recordset destination is an ADO recordset that is stored in a variable of type ‘Object’. Therefore it can be used as the enumerator in a Foreach Loop Container.

Example

We need to send data from a central database to different on separate locations. The separate server information is stored in a configuration database. The Data to send to the servers comes from different database with sales and financial data.

ADO recordset stored in a Variable

Variable in the screenshot is called ‘AdoRecordset’ in namespace ‘User’ and of value type ‘Object’.

image

Using the Execute SQL Task

The Execute SQL Task will execute a select statement (or a stored procedure) that collects the necessary data that will be used to cycle through in the Foreach Loop Container. In this case it will read all servers and their connection data.

image

Excute SQL task general setting

ResultSet : full result set

Connectiontype ADO.Net (can be Oledb as well)

image

Execute SQL Task Result Set setting

Result Name: 0

Variable Name: AdoRecordset

image

Foreach Loop Container Collection settings

Enumerator: ‘Foreach ADO enumerator’

ADO object source variable: User::AdoRecordset, check ‘rows in the first table’.

image

Foreach Loop container Variable Mappings

Here the columns in the recordset need to be mapped to variables in the package.

Variables are created as needed

image

image

Variable: ServerId, Index 0

Variable: ConnectionString, Index 1

image

The connectionString variable

Is used as an expression in a Connection object. The connection object will be used in various dataflow inside the Foreach Loop container

image

Remote server Expression properties: use @User::ConnectionString as ConnectionString property.

image

This will allow to use the same connection in each iteration of the foreach loop with another connectionstring.

Using the Recordset Destination in a Dataflow instead of Execute SQL Task

Instead of a single select statement in an Execute SQL Task we will use a DataFlow with a combination of various sources to combine in a Recordset Destination. The Recordset destination is stored in the variable AdoRecordset.

In the control flow the Execute SQL Task is replaced by a Data Flow Task.

image

The Data flow task reads data from several databases and combines it into a Recordset Destination

image

The recordset Destination editor

VariableName: AdoRecordset.

image

Conclusion

When the Ado recordset for a foreach loop is composed of data from various databases or other Data Flow sources, or when complex decision logic is needed, use a Data Flow with a Recordset Destination instead of an Execute SQL Task to create an in memory recordset that is consumed by the foreach Loop container.

Wednesday, September 18, 2013

Repeat header rows on each page not working

The header rows for a simple tablix do not repeat on each page by default. Since I keep forgetting the solution, I posted the answer that I found on the internet here.

Posted by Stacia on http://stackoverflow.com

“It depends on the tablix structure you are using. In a table, for example, you do not have column groups, so Reporting Services does not recognize which textboxes are the column headers and setting RepeatColumnHeaders property to True doesn't work. Instead, you need to open Advanced Mode in the Groupings pane: click the arrow to the right of the Column Groups and select Advanced Mode. You'll see Static Groups appear in the Row Groups area. Clicking on a Static group highlights the corresponding textbox in the tablix. For the column headers that you want to repeat, select the Static group that highlights the leftmost column header. This is generally the first Static group listed. In the Properties window, set the RepeatOnNewPage property to True. Make sure that the KeepWithGroup property is set to After. The KeepWithGroup property specifies which group to which the static member needs to stick. If set to After then the static member sticks with the group af-ter, or below, it acting as a group header. If set to Before, then the static member sticks with the group before, or above it, acting as a group footer. If set to None, Reporting Services decides where to put the static member. Now when you view the report, the column headers repeat on each page of the tablix.”