|
Return to Newsletter Contents...
Simple SSIS Row Count Logging
by: Sean Beal, Sr. Consultant, CRCP, MCSE
Introduction
SQL Server Integration Services (SSIS) provides a variety
of built-in log providers, and also provides the ability to create custom log
providers. These providers allow us
to capture information about the package as it executes, allowing
troubleshooting and auditing.
However, they do not provide access to row count information.
In this article, we’ll present a ‘quick and dirty’ method for capturing
row count information in SSIS packages.
While SSIS does not provide access to row count information
directly through its logging mechanisms, it does provide the Row Count Data Flow
Transformation. This transformation
does just what its name implies: it counts rows as they pass through a data
flow. We can then store the captured
row counts in a package variable, and write values out to various storage
mechanisms.
Step by Step
Let’s build a sample package to illustrate this.
We’ll create a data flow that moves records from one database to another,
capture the number or rows copied in a variable using the Row Count transform,
and write that information out to an audit table using an Execute SQL Task.
First, we’ll create our variable by clicking ‘Add Variable’
in the Variables window. Keep the
default value of Int32, and name it appropriately:
Now we’ll create our database connections by right-clicking
on the Connection Managers tab and choosing ‘New OLEDB Connection’.
Fill out the appropriate information – in my case, I chose two databases
on my local instance of SQL Server.
Next, drag a Data Flow task and Execute SQL task onto the Control Flow
workspace, connecting them as shown:
Double clicking on our Data Flow Task takes us to the Data
Flow workspace. Here, we’ll need an
OLEDB Source, an OLEDB Destination, and a Row Count transform.
Connect the OLEDB Source to the table from which you would like to pull
data; connect the OLEDB Source’s success output to the Row Count transformation,
and the Row Count transformation’s output to the OLEDB Destination, as shown:
Notice that the Row Count transformation is showing an
error state – this is because we need to configure the transformation to store
the row count in the variable we create earlier.
Double click the Row Count transformation and configure the VariableName
property by selecting the variable:
The row count will now be captured in our variable, but we
need to store that information somewhere in order for it to be useful.
For this example, we’re going to create an audit table on destination
database and store the information there using the Execute SQL Task we added
earlier. Here is the statement used
to create the audit table.
create table audit (
audit_id int identity(1,1) not null
,package_name varchar(50)
,row_count int
)
Next we’ll configure our Execute SQL Task to issue an
insert statement, linking a query parameter to our row count variable.
Add the following insert statement to the SQL Statement property of the
Execute SQL Task:
insert into audit (
package_name
,row_count
)
select ?, ?The
question marks are placeholders for the parameters we will map.
One will be to the variable we created for storing the row count, and the
second will leverage a system variable that contains the package name.
Configure the Parameter Mapping section of the Execute SQL Task as shown
below:
That’s it! When
you execute the package, you should see a row in the audit table showing the
package name and the number of rows loaded.
Conclusion
The use of row counts, whether for informational purposes
only or for control totals, is a common requirement of ETL work.
I hope this simple example will help you implement this functionality
more complex ways in your own SSIS Packages.
Go to Top |
Return to Newsletter Contents
|
