ssis package execut sp with variable

Question

I using ssis package.I want insert flat file source (text file) to sql. Addres of text file is dynamic so i define variable for path.I have the sp like this:

CREATE PROCEDURE [dbo].[Insert_FileMaster]
  @FILE_PATH   nVARCHAR(MAX) 
 ,@id      int OUTPUT
AS
BEGIN
 insert into [dbo].[FileMaster] ([FM_Name])
 values(@FILE_PATH))

 set @id = @@IDENTITY
END

I want exec this sp With variable parameter.

this is my package:

Which ssis tool should i use?and how to get output from sp (return parametert must be use in another sp in package)?

Answer 1

You will want to add an Execute SQL Task before your Data Flow Task (this will be at the Control Flow level).

You will need to configure the Execute SQL task as described in this answer.

Insert a single row and return its primary key