Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Get the count from the rowcount transform

Posted by thomasivarssonmalmo on November 27, 2007

This is a blog regarding how you can write the value of a rowcount transform to a simple log table. It was not that easy to find information about this even in SSIS books and I did not like the option to use script for this since I prefer a simple standard solution.
 
Start with building this simple log table in the database of you choice.
 
Create Table SimpleLog
( DataRowCount int,
  InsertedDate datetime)
 
We will simply export the content of the DimTable in the AdventureWorksDW database part of the samples when you install SQL Server 2005.
 
Do a simple Select * From DimTable.
 
Start management studio and connect to this database. Under tools and options in the meny you can check Query Results, SQL Server and Results to text. For output format I have choosen comma delimited. Choose export to file for the result set and save it as a *.txt-file.
 
Management studio
 
 
Now we can start SSIS in BIDS. First we will start by creating a variable that will hold the value of the rowcount transform. Right click in the area of the controlflow if the variable window is not seen to the left. Create a new variable with the name NoTimeRecords and set the data type to int.
 
After that you choose tools and pick the data flow task  and place it in the control flow. Click on this controlflow to go to the data flow. Set up a connection to your file, that you previously created with a flat file source connector.
 
Flat file connection
 
 
 Then you add a row count transform and connect it to the flat file source.
 
Rowcount
 
In here you only connect variable name property to the variable that you created previously.
 
Next you create an OLEDB destination, connect it to the rowcount and create a destination table.
 
 Finally we are at the point where we will write the value of the variable to the simple log table.
 
 Go to the eventviewer tab and choose the dataflow task you have created, not the package. Choose the OnPostExecute eventhandler to the right and click on the link in the middle of the pane. Drag an Execute SQL task from tools to the pane.
 
EventhandlerSQL
 
 
Paste this SQL into the SQLStatementbox and configure the general tab as in the picture below.
 
Insert Into SimpleLog
(RecordRowCount, InsertedDate)
Values(?,GetDate())
 
General
 
On the parameters tab you configure the Execute SQL task according to the last picture.
 
Parameters
 
 
Finally we can execute the task and check the SimpleLog table for a record.
 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: