How to Return Deleted and Update Record Count from Execute SQL Task and Write to Flat File in SSIS Package - SQL Server Integration Services ( SSIS) Tutorial

Scenario:

We want to create an SSIS Package in which we will be using a Stored Procedure, which will delete and update some records and return us the delete and update record count. Once the Stored Procedure is completed, we want to log the record count to a file file for our reference. We will append the record to flat file.

In this video we will learn how to returned Number of records deleted or updated by a Stored Procedure that we called in Execute SQL Task.

You will be able to learn following items from this video demo

1. How to Create SSIS Package from Scratch
2. How to create variables to hold the values from Execute SQL Task
3. How to Create Stored Procedure that will return Delete and Update Record Count
4. How to Map Store Procedure output to variables in SSIS Package
5. How to Write Variable values to Flat File

Script to get Deleted and Update Record Count from Execute SQL Task

use test
go
Select * from dbo.Customer_AS
Select * from dbo.Customer_NA


--drop procedure dbo.usp_UpdateCustomer
Create procedure dbo.usp_UpdateCustomer
AS
BEGIN

Declare @UpdateCnt INT
Declare @DeleteCnt INT

--Update Records in Customer_AS
update CAS
set CAS.LastName=CNA.LastName
from dbo.Customer_AS CAS
inner join dbo.Customer_NA CNA
on CAS.ID=CNA.ID

SET @UpdateCnt=@@ROWCOUNT

Delete from dbo.Customer_AS
where FirstName in ( Select FirstName from dbo.Customer_NA)
SET @DeleteCnt =@@ROWCOUNT

Select @UpdateCnt as UpdateCnt,@DeleteCnt AS DeleteCnt
END

Get Delete/Update Record Count from Execute SQL Task and Write to Flat file in SSIS Package

 Related Posts / Videos on Execute SQL Task 

• Execute SQL Task Demo for Max Size of SQL Statement in Execute SQL Task Query Editor and Solution
• Execute SQL Task Demo- How to Build Dynamic SQL Query to Execute Multiple Stored Procedures those names are saved in a SQL Server Table
• Execute SQL Task Demo - How to Execute SQL Queries from a SQL Server Table in SSIS Package ( SQLStatementType= Variable)
• Execute SQL Task Demo - How to Execute SQL Queries from Excel file in SSIS Package ( Record Set Destination, Object Type Variable and Foreach Loop Container Demo as well)
• Execute SQL Task Demo - How to use Single Row Result Set in Execute SQL Task and Control the Flow Tasks by using that value ( Precedence Constraint Demo as well).
• Execute SQL Task Demo - How to use Insert Query in Execute SQL Task and Map the variables to Parameters ( Insert File Name and Record Count after Loading the File).
• Execute SQL Task Demo - How to use Stored Procedure with Input/Output Parameters in Execute SQL Task in SSIS Package( Load File Name, Package Name , Record Count and File Load Time in SQL Server Table).
• Execute SQL Task Demo - How to build Query by using Variables in Execute SQL Task ( File Name validation and move files to Archive folder and Bad File Folder if does not validation correctly)
• Execute SQL Task Demo - How to use Full Result Set in Execute SQL Task ( Get Folder Path and File Names from SQL Table and Load those files)
• Execute SQL Task - How to Load Files from Specific Folder Paths Saved in a SQL Table By using SSIS Package
• Execute SQL Task ( use Variable value in Expressions Demo) - How to Reject Already Loaded Files and Load Only New Files in SSIS Package
• Execute SQL Task ( Handle Single Row Result Set Blank Error) -Single Row result set is specified, but no rows were returned in Execute SQL Task in SSIS Package
• Record Count from Execute SQL Task and Write to Flat File in SSIS Package
• Execute SQL Task ( Build Dynamic Query in Variable and use as Source ) - How to Save Query in Variable and Use in Execute SQL Task 
• Execute SQL Task Expression's Demo - Using Variable in IF Clause in Execute SQL Task in SSIS Package
• Execute SQL Task ( Parameter Mapping Demo for Stored Procedures) - How to Run multiple Stored Procedure in Execute SQL Task with Input Parameters
• Read Single Cell Value from an Excel File in SSIS Package