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,
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.
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.
Then you add a row count transform and connect it to the flat file source.
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.
Paste this SQL into the SQLStatementbox and configure the general tab as in the picture below.
Insert Into SimpleLog
On the parameters tab you configure the Execute SQL task according to the last picture.
Finally we can execute the task and check the SimpleLog table for a record.