CODEWITHSUNDEEP
sqlsql-serverssisetlrowcount
Isha
Isha

Reputation: 121

Record rows updated through SSIS execute task

I have a package which connects to SourceDB1 and execute an update statement that updates DestDB2. Now I have many such source DB and destination DB which has the similar structure. For this I have for each loop container within which the execute SQL task is placed. Now, since the sourceDB connection varies , I have created a variable named SourceConnString. Likewise, the update query is also within a variable named UpdateVariable. I created UpdateVariable because source and destination are two different databases.

Now I need to save the number of records that are updated in the execute sql task each time. The records count should be inserted into a table or flat file whichever is easy.

I saw tutorials how to do this without using an update through variable. Is there any way we could return the count of rows in my situation.

Upvotes: 2

Views: 6064

Answers (3)

Isha
Isha

Reputation: 121

I did the following. A new variable “Newupquery” is created and below code snippet is the value assigned to this variable and which is used within the execute SQl task. 

DECLARE @UpdateRowCnt INT;
update countcheck
set [Base Unit of Measure] = 'PCS-1'
where [Base Unit of Measure] = 'PCS';
SELECT @UpdateRowCnt=@@ROWCOUNT;
Select @UpdateRowCnt

The above execute SQl task is executed and the result set is set to variable “UpdateRowCount”.

The next step is another Execute SQL Task, which inserts the value of the variable UpdateRowCount to the log table in a stage server. The value of variable UpdateRowCount was passed to insert statement using the parameter setting in Execute SQl Task.

Upvotes: 1

Hadi
Hadi

Reputation: 37313

You can use a Select query with @@ROWCOUNT after the UPDATE query, like the following:

UPDATE SomeTable Set SomeColumn = SomeValue;

SELECT @@RowCount as NumberOfAffectedRecords

And use a Single Row ResultSet to get the value.

The following article contains a Step-by-step tutorial

Upvotes: 3

Ferdipux
Ferdipux

Reputation: 5246

You can use ExecValueVariable property, in Execute SQL Task it stores number of records affected by SQL command. You have to specify variable name at ExecValueVariable property or pick it up from drop-down as shown below. enter image description here

Upvotes: 4

Related Questions