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 


5 comments:

  1. Thank you so much for providing such an interesting and useful knowledge about SSIS and its very useful aspects.SSIS operations are always useful when there is a need to break down complex IT problems into simple solutions.

    SSIS PostgreSql Write

    ReplyDelete
  2. Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time
    sql server dba online training
    SQL dba online course

    ReplyDelete
  3. TODAY I GOT MY DESIRED XMAS LOAN AMOUNT $520,000.00 FROM A RELIABLE AND TRUSTED LOAN COMPANY. IF YOU NEED A LOAN NOW EMAIL CONTACT drbenjaminfinance@gmail.com

    Hello, I'm here to testify of how i got my loan from BENJAMIN LOAN FINANCE(drbenjaminfinance@gmail.com) I don't know if you are in need of an urgent loan to pay bills, start business or build a house, they offer all kinds of loan. So feel free to contact Dr. Benjamin Owen he holds all of the information about how to obtain money quickly and painlessly without cost/stress via Email: drbenjaminfinance@gmail.com

    Consider all your financial problems tackled and solved ASAP. Share this to help a soul right now THANKS.

    ReplyDelete
  4. Hello Everyone,

    Welcome to the future! Financing made easy with Prof. Mrs. DOROTHY LOAN INVESTMENTS

    Have you been looking for financing options for your new business plans, Are you seeking for a loan to expand your existing business, Do you find yourself in a bit of trouble with unpaid bills and you don’t know which way to go or where to turn to? Have you been turned down by your banks? MRS. DOROTHY LOAN INVESTMENTS says YES when your banks say NO. Contact us as we offer financial services at a low and affordable interest rate of 2% for long and short term loans. Interested applicants should contact us for further loan acquisition procedures via profdorothyinvestments@gmail.com

    Services rendered include:

    * Refinancing Loans
    * Car Loan
    * Truck Loans
    * COVID-19 Financing Loan
    * Home Loan
    * Mortgage Loan
    * Debt Consolidation Loan
    * Farm Loan
    * Business Loan [secure and unsecured]
    * Personal Loan [secure and unsecured]
    * Students Loan and so many others.

    For more info; Contact us via Email: profdorothyinvestments@gmail.com

    With Prof. Mrs. DOROTHY LOAN INVESTMENTS. you can say goodbye to all your financial crisis and difficulties as we are certified, trustworthy, reliable, efficient, fast and dynamic

    ReplyDelete
  5. Thank you for sharing this amazing article with us. Here I am sharing a great resource .
    download igoal88

    ReplyDelete