tsql - ForEach Loop Container Mapping Variable From SSIS Package Not Working In SQL Server stored procedure -

i have ssis package uses foreach loop container enumerate excel files in dir. have task flow inserts data excel files sql server.

i'm trying insert file names column same table in sql server using mapping variable in stored procedure.

i'm having trouble mappingvariable @ end of script red squigglies. following stored procedure script.

create procedure [dbo].[insert_f_stg_v2]     @hrs float,     @type nvarchar(100),     @sn nvarchar(100),     @op nvarchar(100),     @[user::currentfilename]   begin     set nocount on;      insert [crm_rc].[dbo].[f_stgtbl]([hrs], [type], [sn], [op], [report_date])     values (@hrs, @type, @sn, @op, @[user::currentfilename]) end 

the last @[user::currentfilename] in values block @ bottom of script 1 giving me issues.

the following error:

msg 102, level 15, state 1, procedure insert_f_stg_v2, line 95
incorrect syntax near 'user::currentfilename'.

you need add derived column data flow, , assign user::currentfilename new column, include new column in column mapping.

in stored procedure, change @[user::currentfilename] proper sql type such @filename nvarchar(255).

suggestion: away stored procedure, , insert directly table (would faster, since bulk inserted) using sql destination instead of executing procedure every row.