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


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
Select * from dbo.Customer_AS
Select * from dbo.Customer_NA

--drop procedure dbo.usp_UpdateCustomer
Create procedure dbo.usp_UpdateCustomer

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


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

Select @UpdateCnt as UpdateCnt,@DeleteCnt AS DeleteCnt

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

 Related Posts / Videos on Execute SQL Task 

No comments:

Post a Comment