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
- How to Create SSIS Package from Scratch
- How to create variables to hold the values from Execute SQL Task
- How to Create Stored Procedure that will return Delete and Update Record Count
- How to Map Store Procedure output to variables in SSIS Package
- 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
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