ForEach Loop Container Mapping Variable From SSIS Package Not Working In SQL Server stored procedure
I have a SSIS package that uses a ForEach Loop Container to enumerate Excel files in a dir. I also have a Task Flow that inserts data from those Excel files into SQL Server.
I'm trying to insert the file names into a column into the same table in SQL Server by using a mapping variable in my stored procedure.
I'm having trouble with my MappingVariable at the end of the script with red squigglies. The following is my stored procedure script.
CREATE PROCEDURE [dbo].[Insert_F_STG_v2] @Hrs float, @Type nvarchar(100), @SN nvarchar(100), @Op nvarchar(100), @[USER::CurrentFileName] AS BEGIN SET NOCOUNT ON; INSERT INTO [CRM_RC].[dbo].[F_StgTbl]([Hrs], [Type], [SN], [Op], [Report_Date]) VALUES (@Hrs, @Type, @SN, @Op, @[USER::CurrentFileName]) END
The last @[USER::CurrentFileName] in the values block at the bottom of the script is the one giving me issues.
The following is the error:
Msg 102, Level 15, State 1, Procedure Insert_F_STG_v2, Line 95 Incorrect syntax near 'USER::CurrentFileName'.
You need to add a derived column to your data flow, and assign User::CurrentFileName to the new column, then include this new column in your column mapping.
In your stored procedure, change @[USER::CurrentFileName] to a proper SQL type such as @filename nvarchar(255).
Suggestion: You could also do away with the stored procedure, and insert directly into the table (would be faster, since its bulk inserted) by using a SQL destination instead of Executing a procedure for every row.
@[USER::CurrentFileName] is not a valid parameter name, and you don't have datatype assigned to it. SQL Server is treating [USER::CurrentFileName] as a datatype and therefore failing.
In the end it appears you are trying to insert that value into [Report_Date] column. Which I'm not sure how Date and Filename would be compatible.
What you need to do is create as second Variable that parses the useful information out of your file name. In a separate variable parse the date from @[USER::CurrentFileName] then use that Variable in this proc. I would be more specific but I don't know how your file is named.