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)?
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.