I am using a merge script to merge the records into target table. Script is working fine and package is running successfully. I want to capture the numbers of rows merged into target table (update+delete+insert) and log into log table. How can i assign the number of rows merged to a variable OR please suggest any other way to capture this number. Thanks

Hi, Are you using the Merge statement in stored procedure to perform this merge task, or are you using the SSIS DataFlow Merge Task ?Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

You define 3 different variable uVar_insert, uVar_update uVar_delete and use a ROW COUNT ( see toolbox) and finally use a SQLTask to insert the 3 values into a table Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

I am using a merge script to merge the records into target table. Script is working fine and package is running successfully. I want to capture the numbers of rows merged into target table (update+delete+insert) and log into log table. How can i assign the number of rows merged to a variable OR please suggest any other way to capture this number. Thanks Did not try in SSIS, but in T-SQL, there is a command OUTPUT. In short, to capture those rows that say were inserted you can do MERGE ... blah ... OUTPUT INSERTED.* then capture the result of the merge (done in Execute SQL Task) into an Object Variable data type of SSIS. PS: Change the thread type to issue, not a post as it is now.Arthur My Blog

I am using a script only starting from Merge into...... Not a stored procdure.

Arthur Currently, i am doing half of what you said. I am using Merge script (Tsql) in execute sql task. I f i am not wrong, i have to map the result set to FULL RESULT SET in execute sql task itself and then assign it to a variable.

Did not try in SSIS, but in T-SQL, there is a command OUTPUT. In short, to capture those rows that say were inserted you can do MERGE ... blah ... OUTPUT INSERTED.* You are right thats the best Idea, he is using scriptSincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Arthur Currently, i am doing half of what you said. I am using Merge script (Tsql) in execute sql task. I f i am not wrong, i have to map the result set to FULL RESULT SET in execute sql task itself and then assign it to a variable. Yes, you do, you are right. Reminder: change the thread type.Arthur My Blog

not getting what you said in REMINDER

Nik,I need to define the three variables in the merge statement?

Arthur, In the merge statement (tsql), do i have to define any variables OR 1. just the merge statement 2. then create a variable (say count) in ssis in variable tab and make it dataye as object 3. assgin the merge statement in execute sql task and result set to full result set. 4. map the variable to result set as result set name = 0 and variable name = 'count' 5. use a another task (may be script task) log the value of the variable in the log table. DO THE VARIABLE WILL HAVE THE COUNT OF MERGED ROWS?

Nik,I need to define the three variables in the merge statement? Define??? i am not sure what you want but i assume that you will need... define 3 variables use them in the derived column and then use them in your mergSincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Harry, I would strongly recommend that you create a stored procedure and encapsulate the Merge Statement in it, rather than having inline TSQL within the Execute SQL Task of your SSIS package. Within this Stored proc, use OUTPUT and copy the INSERTED. , UPDATED. & DELETED. results set into temp tables. Do a row count on each of these temp tables and stored them into 3 local variables in the Proc. Return them back to the EXECUTE SQL task via OUTPUT Parameters and have them mapped to corresponding SSIS Package Variables. One you have them SSIS package Variables populated with the said rowcounts in this fashion - now you are free to use them as you like - log them in a file, table or send email alerts, etc. Hope this makes it clear for you.Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com