sql-serverssisssis-2012

ssis package execut sp with variable


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. variables window

this is my package:

enter image description here

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


Solution

  • 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