We specialize in the implementation and optimization of large scale databases in the Tera-byte range. Enterprise Application Skills is an independent consulting firm dedicated to supporting the community via Microsoft technologies.
SSIS Data Flow component used to log record counts in a data flow task via SSIS's native log providers.
Thanks for everyone who attended the Malibu SQL Server User Group
The slides are here. I will be posting some of the source code and video if the quality is descent enough.
Real-Time Insights & Complex Event Processing – SQL Server StreamInsight & the Real-Time Web
Discover the power of SQL Server StreamInsight for processing continuously arriving data and providing analytics and insight at near-real-time. Real-time operational intelligence provides a real-time view of business operations. Imagine being able to recommend or adjust the content of your website based on what you know about your user, in real-time. In this session you’ll learn the basics of StreamInsight’s architecture and we’ll walk through an example of how it can be applied. If time permits, we’ll also look at an application of StreamInsight with other cutting-edge Microsoft technologies that empower the Real-Time Web, namely, SignalR.
This method takes advantage of SQL Server’s new ranking functions and can be used in your SQL Reports to determine the page number in the body of a report and also possibly act upon that information to hide/display columns.
Use this method if:
Note: You can download the code for the article from the attachments at the end of the article
I’ll give a quick overview of the ranking functions for clarity, for a more detailed dive check out my article on Ranking Functions.
If you don’t need the review, skip to the good part:
So I mentioned there were 3 scenarios where this could work for you. I’ll give an example on how to do each and let your creativity do the rest. This will server as our sample sales table:
|name||sales_amount||group_name||Scenario #1||Scenario #2||Scenario #3|
You have a preset # records to be places on each page:
Here’s what your SQL Query would look like:
DECLARE @rows_per_page INT SET @rows_per_page = 2 SELECT employee.name ,page = (Row_Number() OVER ( ORDER BY employee.sales_amount ) / @rows_per_page) FROM employee
In SSRS you can then create a parent group in your tablix (SSRS 2008) and set the option to break “Between each instance of a group”. (Page Breaks tab in SSRS 2008)
You have grouping and each group shows on its own page
SELECT employee.name ,page = Dense_Rank() OVER ( ORDER BY employee.group_name ) FROM employee
On your report in SSRS, on the group properties for the table/matrix/tablix set the option to break “Between each instance of a group”. (Page Breaks tab in SSRS 2008)
You can accurately calculate the number of records on each page
If you can accurately calculate the number of records on each page, it becomes an easy task to use a similar methods as in #1. In the example, the number was 4 records per page.
Let’s say you wanted to number pages and reset page numbering for each group of data you have. You also want to display the total number of pages as in Page x of y per group. This example was inspired by this post
Assuming you fall under scenario #1 where you know the number of records per page (2 in our example)
SELECT group_name ,total_number_of_pages_per_group = CEILING(COUNT (*) / @rows_per_page) FROM employee GROUP BY group_name
DECLARE @rows_per_page INT SET @rows_per_page = 2 ;WITH total_pages -- CTE AS ( SELECT group_name ,total_number_of_pages_per_group = CEILING(COUNT (*) / @rows_per_page) FROM employee GROUP BY group_name ) SELECT employee.name ,employee.group_name ,page = (Row_Number() OVER (PARTITION BY employee.group_name ORDER BY employee.sales_amount ) / @rows_per_page) + 1 -- page x ,total_pages.total_number_of_pages_per_group -- of y FROM employee INNER JOIN -- Join to get page x of y total_pages ON total_pages.group_name = employee.group_name
Note: You can download the code for the article from the attachments
What makes FTP Task ++ so special?
FTP Task++ is used in the control flow as any other stock control flow task.
Double click on the task to bring up its properties and customize its behavior.
To install the component, run the installer (MSI)
To add it to your toolbox in BIDS, right click on the toolbox and click on “Choose Items”
Fig 4: Visual Studio Toolbox, adding FTP Task++
From there, click on the “SSIS Control Flow Items” tab and select FTP Task++ from the list of components.
RowCount++ is a free SSIS Data Flow component used to natively log record counts processed. RowCount++ comes in handy because logging record counts is not available out of the box. Microsoft’s stock Row Count component in SSIS allows you to save row counts in a variable but doesn’t have the ability to read custom data from variables and persist it using any of the SSIS logging providers.
The component fires an OnRowCount event that can be consumed by the SSIS engine. When the component finishes counting records from all input buffers it checks if the OnRowCount event was selected, which is the default behavior. The event is fired and can be captured by SSIS. You can capture the event using any of the log providers as with the OnError event.
1. Drop RowCount++ in-between you source and destination.
Figure 1: RowCount++ in SSIS Package
2. Use SSIS Logging to capture the OnRowCount event just as you would for a regular OnError event.
Figure 2: RowCount++ SSIS Configuration
To install the component, run the installer project (MSI).
1. To add it to your toolbox in BIDS (Visual Studio), right click on the toolbox and click on “Choose Items”
Figure 3: RowCount++ in Visual Studio Toolbox
2. From there, click on the “SSIS Data Flow Items” tab and select RowCount++ from the list of components.
Figure 4: RowCount++ Visual Studio Toolbox: Add Items
3. RowCount++ will appear in the toolbox when in the data flow tab
SQL Server 2005 introduced new ranking functions. This article is tailored as an introduction to these functions, difference between them and when to use each and a few examples.
The new ranking functions are new internal functions to SQL Server 2005/2008.
From MSDN “Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.”
In simple terms, ranking functions allow you to sequentially number your result set. Your result set can be partitioned so the numbering essentially resets for each partition for example you can get the sales rank of employees partitioned by their department, or manager etc..
What’s worth mentioning is that ranking functions are non-deterministic so you cannot use them in something like an indexed view.
This will serve as our sample table for all our examples. You can download the script to create the table and populate it with the data used in the examples.
ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> )
Returns the row number of the result set for each row in a partition based on the order provided in the order by clause.
RANK () OVER ( [ <partition_by_clause> ] <order_by_clause> )
Similar to Row_Number() only Rank determines the position, or lack for a better word, ranking of each row based on the Order By clause. Rank is usually used with the Partition clause to cluster your result sets. Rank also skips numbers, if 2 or more records tie in value, they will receive the same rank. The following rank would 1+ the total number of records in the same partition so for example (1,2,2,2,5,6)
DENSE_RANK () OVER ( [ <partition_by_clause> ] <order_by_clause> )
Same as Rank() only guarantees consecutive integers (No skipping) (1, 2, 2, 2, 2, 3). If a tie occurs, it will sort arbitrarily (based on the execution plan and indexes used) and continue.
NTILE (integer_expression) OVER ( [ <partition_by_clause> ] <order_by_clause> )
Used to distribute the rows in an ordered partition into x number of groups. Each row receives the group number it belongs to.
Example: Simple ORDER BY clause
SELECT name ,territory ,ROW_NUMBER() OVER ( ORDER BY sales_amount ) AS [row_number] ,RANK() OVER ( ORDER BY sales_amount ) AS [rank] ,DENSE_RANK() OVER ( ORDER BY sales_amount ) AS [dense_rank] ,NTILE(4) OVER ( ORDER BY sales_amount ) AS [ntile] FROM sales_employee
Example: Using PARTITION BY
SELECT name ,territory ,ROW_NUMBER() OVER (PARTITION BY territory ORDER BY sales_amount) AS [row_number] ,RANK() OVER (PARTITION BY territory ORDER BY sales_amount) AS [rank] ,DENSE_RANK() OVER (PARTITION BY territory ORDER BY sales_amount) AS [dense_rank] ,NTILE(4) OVER (PARTITION BY territory ORDER BY sales_amount) AS [ntile] FROM sales_employee
Ranking functions can come in very handy when devising groups or sequential numbering of your result sets. Ranking functions are particularly useful in reporting.
Last week I was part of an interesting “call” with Microsoft around SQL Server Modeling Services and what it had to offer. The purpose to get client feedback on the proposition and also provide us with some insight on what it was and where it was heading.