As the name suggests we have taken the current Row Count Transform that is provided by Microsoft in the Integration Services toolbox and we have recreated the functionality and extended upon it. There are two things about the current version that we thought could do with cleaning up
- Lack of a custom UI
- You have to type the variable name yourself
In the Row Count Plus Transformation we solve these issues for you.
Another thing we thought was missing is the ability to calculate the time taken between components in the pipeline. An example usage would be that you want to know how many rows flowed between Component A and Component B and how long it took. Again we have solved this issue.
Credit must go to Erik Veerman of Solid Quality Learning for the idea behind noting the duration. We were looking at one of his packages and saw that he was doing something very similar but he was using a Script Component as a transformation. Our philosophy is that if you have to write or Copy and Paste the same piece of code more than once then you should be thinking about a custom component and here it is.
The Row Count Plus Transformation populates variables with the values returned from;
- Counting the rows that have flowed through the path
- Returning the time in seconds between when it first saw a row come down this path and when it saw the final row.
It is possible to leave both these boxes blank and the component will still work.
All input columns are passed through the transformation unaltered, you are not permitted to change or add to the inputs or outputs of this component.
Optionally you can set the component to fire an event, which happens during the PreExecute phase of the execution. This can be useful to improve visibility of this information, such that it is captured in package logging, or can be used to drive workflow in the case of an error event.
Properties
| Property |
Data Type |
Description |
| OutputRowCountVariable |
String |
The name of the variable into which the amount of row read will be passed (Optional). |
| OutputDurationVariable |
String |
The name of the variable into which the duration in seconds will be passed. (Optional). |
| EventType |
RowCountPlusTransform.EventType |
The type of event to fire during post execute, included in which are the row count and duration values. |
RowCountPlusTransform.EventType Enumeration
| Name |
Value |
Description |
| None |
0 |
Do not fire any event. |
| Information |
1 |
Fire an Information event. |
| Warning |
2 |
Fire a Warning event. |
| Error |
3 |
Fire an Error event. |
The component is provided as an MSI file, however to complete the installation, you will have to add the transformation to the Visual Studio toolbox manually. Right-click the toolbox, and select Choose Items.... Select the SSIS Data Flow Items tab, and then check the Row Count Plus Transformation from the list. You may need to restart Business Intelligence Development Studio (Visual Studio) or even the computer for the control to be correctly registered.
Downloads
The Row Number Transformation is available for both SQL Server 2005 and SQL Server 2008, with full support for seamless upgrades. Please choose the version to match your SQL Server version, or you can install both versions and use them side by side if you have both SQL Server 2005 and SQL Server 2008 installed.
Row Count Plus Transformation for SQL Server 2005
Row Count Plus Transformation for SQL Server 2008
Version History
SQL Server 2008
Version 2.0.0.5 - SQL Sedrver 2008 release.
(15 Oct 2008)
SQL Server 2005
Version 1.1.0.43 - Bug fix for duration. For long running processes the duration second count may have been incorrect.
(8 Sep 2006)
Version 1.1.0.42 - SP1 Compatibility Testing. Added the ability to raise an event with the count and duration data for easier logging or workflow.
(18 Jun 2006)
Version 1.0.0.1 - SQL Server 2005 RTM. Made available as general public release.
(20 Mar 2006)
Screenshot
