Hello friends, today I will talk about Rowcount task in SSIS. Using this task we can find out if our dataset contains some rows or not. To implement Rowcount task, we will create a table and insert few rows into it. We will use below SQL statement to create the table.
Create table Emp(Eid int,Name Varchar(50))
Now we will insert few rows into “Emp” table using below SQL statements.
insertinto Emp values(115,‘Mohan Sharma’)
insert into Emp values(116,‘Priyanka Gandhi’)
insert into Emp values(107,‘Akash Rajput’)
insert into Emp values(108,‘Vidushi Pandey’)
insert into Emp values(109,‘Shivika Garg’)
insert into Emp values(110,‘Vikas Sharma’)
First of all we will add “Data Flow” task into control flow region as shown in the below screenshot.
We will double click on the data flow task that will take us to the data flow region. In data flow region, we will add “OLEDB Source” as shown in the below screenshot.
To configure the “OLEDB Source” we will right click on the “OLEDB Source” and configure the connection manager and select the “Emp” table as a data source(please refer below screenshot for your reference).
We will click on “OK” button to close the above screen and add “Rowcount” task to the “Data Flow” region(shown in the below screenshot).
Now we need to create a variable that will hold the “Rowcount” value returned by the “Rowcount” task.To create the variable we will right click on the “Control Flow” region and click on the “Variables” menu and create a variable named “Rowcount”.
Below is the screenshot in which you can see the created variable.
Now we will configure the “Rowcount” task, to configure the “Rowcount” task, we will right click on it and click on the “Edit” menu that will bring a new screen to us( As shown in the below screenshot).
In the above screenshot , we can see there are three tabs(Component properties, Input Columns, Input and Output properties). First of all, we will select the “Component properties” tab and assign the variable “Rowcount” to “VariableName” field.
We will click on “OK” button to close the above window. Now we will add “Script” task in the “Control Flow” region to see the output.
To configure the “Script” task, we will right click on it and click on “Edit” menu that will bring a new screen to us( As shown in the below screenshot).
We will click on the button(encircled by red color) that will open a new screen as shown in the below screenshot.
We will click on “OK” button to close the above window. Now we are back to the previous screen. We will click on “Edit Script” button that will open the “Visual Studio IDE” and there we will write the below code.
We will close the IDE and click on the “OK” button of “Script Editor” that will close the script editor window. Now we are done with our package. Lets run the package by pressing “F5” key and see the result.
In the above screenshot, we can see a message box has come up with a number 6 showing the rowcount of the data set. I hope you will find this article useful. Thank for reading my blog.
1,019 total views, 1 views today