Row Count Transformation in SSIS

Suppose you want to find the number of row from flat file or table we need to write the custom code for the same. For finding the number of row of the table is bit easy but think about flat file. For that we need to write the custom code. It is somewhat complex task to make it easy SSIS provide a transformation know as Row Count.

The Row Count Transformation counts the number of rows that have passed through a Data Flow and puts that count into a variable.

Let’s learn step by step how to implement the row count transformation in SSIS

 Here I am taking one example and implementing row count. I am having a flat file and want to find the number of row in that flat file and want to print the count through script task.

Open the SSDT (Sql server data tool)

Take data flow task

 

Double click on that you will be redirected on data flow pan. Here I am taking flat file as the source.

Need to configure the connection manager for the flat file source.

Click ok.

Map the column.

Click ok.

Now we need to declare the variable on package label on that we need to store the row count value.

Right click on pan you will get the pop up on that you click on the variable

Declare the variable.

Now I am taking Row count transformation

  

Double click on the row count transformation. You will get the Row count prompt. Select the row count variable.

Note: In SSDT you will get this prompt. But if you are using BIDS you will get below prompt.

Now click on ok.

Now I am taking script task on control flow pan to display  the value of row count.

Double click on the Script task.

Select the Read only values from the user define variable as you see on above.

Click on ok.

Now click on Edit script task

Visual studio will be open

In main method we are writing our custom code.

In main method I am write code to display the value of row count.

C# Code

public void Main() { // TODO: Add your code here MessageBox.Show("Number of Row in flat file is :  +Dts.Variables["User::RowCount"].Value.ToString()); Dts.TaskResult = (int)ScriptResults.Success; }

Compile and Save it and close the visual studio.

 

Click ok.

Now it is read to run. I am running this package.

We will get the below result

 

Now see the number of record in flat file

Oh!!!! Getting expected result.

Hope this is helpful to you.

Enjoy  J

Keep tuning with us.