0

I'm using Visual Studio 2015, SSIS to run set of sql tasks in Execute Sql task and then do a data transfer between tables which are in SSMS by executing package in SSIS. When we run a series of sql statements on SSMS, we get results like rows effected for every sql successful activity. However, now I want to automate the process using SSIS to reduce the turn around time. I would like to get the rows effected for every sql query like select, insert, delete which are in execute sql task. How can it be done in SSIS? I don't have dbo_owner permission to stored procedures in SSMS. I'm thinking SSIS would be a quick way. But it is very important for me to make a log of rows effected to validate the data, as it is financial data. I have nearly 10 sql statements in each sql task like select and delete. But the output is only one table.
For example my sql task is like below 

select * from dbo.table1;
select * from dbo.table2 where city = 'Chicago';
create dbo.table3(id int, name varchar(50);
insert into dbo.table3(1,'a');
select * from dbo.table3;

If I execute this in SSMS I get rows effected for each select statement and also table is created. If I execute the same through package in SSIS, how will get messages for each of them?

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
Asrita
  • 1
  • 1

2 Answers2

0

I assume your data lies on SQL Server. With selects, you could use data flow tasks and row counts instead of Excecute Sql's.

For inserts and updates there's a few ways to get affected rowcount, like this: https://stackoverflow.com/a/1834264/5605866

or like this: http://microsoft-ssis.blogspot.fi/2011/03/rowcount-for-execute-sql-statement.html

Basically the same thing but with a bit different syntax.

Gigga
  • 555
  • 2
  • 10
  • How can I use output clause for select statement? – Asrita Nov 17 '17 at 19:01
  • Output is not available for select-statements in SQL Server. You can use Data Flow task and Row Count component for selects, as described by xChaax below. – Gigga Nov 18 '17 at 12:05
0

You can use the Row Count transaformation after the Data source and save it the variable. Can refer to this get the number of rows returned from the Source that SHOULD be processed.

Hope this help.

xChaax
  • 193
  • 5
  • 27