Getting return value from stored procedure dynamically in SSIS

Question

I am using 2 stored procedures in Execute SQL Task in SSIS in a ForEach loop container. From 1st procedure i am returning the value as scope_identity which i want to access in 2nd stored procedure dynamically.Below are the stored procedures. First stored procedure :-

ALTER PROCEDURE [dbo].[insertTm]
      @filename nvarchar(128),
      @date date,
      @count int,
      @fileid int output
AS
BEGIN
      INSERT INTO TmRecruitment(FileName,FileLoadDate,RecordCount)
      VALUES (@filename, @date, @count)
      SET @fileid=SCOPE_IDENTITY()
      RETURN  @fileid
END
I have created appropriate variables and mapped 3 variables as input and 1 variable as output variable to recieve the value of scope identity.
Second stored procedure is :-


ALTER proc [dbo].[toinsertTx]
    (@fileido int)
    as
    begin
    insert into TxRecruitment
    (JobReqID,TemplateName,JobType,JobLevel,JobTitle,HeadcountType,Backfill,RequisitionStatus,Status,CurrentApprover,Department,SubDepartment
    ,CostCenterID,Country,JobPostingLocation,DepartmentHead,SubDeptHead,HiringManager,BudgetController,Recruiter,DateCreated,ApprovedDate   
    ,Age,TimeToReqApproval,NoOfDaysOpen)
    select * from TsRecruitment

    update TxRecruitment set FileId=@fileido
    end

All things I am getting correctly and i am taking the value of scope identity into variable @fileido. I have 3 files and i want scope identity to return the value of FileId each time it is getting one file. But i am getting the return value only one as 3. But i want value to be returned as 1 then 2 and then 3. What should be the right way to do this so that i get the return value differntly each time.

Kapil Kapil · Accepted Answer · 2017-11-07T07:35:10

Use a for each loop container which has two execute sql tasks inside it,first for [dbo].[insertTm] and second for [dbo].[toinsertTx] respectively.You can use For each loop for ADO enumerator if the file name and other input details for [dbo].[insertTm] picked up from a table, if the file name and other details are picked from files, use a file enumerator. This would keep updating the input variables for [dbo].[insertTm] and the output of this SP which has the current scope identity can be fetched and passed to the second SP.

Getting return value from stored procedure dynamically in SSIS

1 Answers