0

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'.

Improve this question

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I like your answer. I would like to do away with the stored procedure and insert directly into the DB...May you please point me in the right direction to do this...Im having a horrible time trying to figure out how to use the mapping variable.
If I use the DerivedColumn Transformation, what do I use as the input? as I used a ForLoop Container to grab the file names?
I also agree you should be moving towards a BULK INSERT Strategy but don't necessarily have enough info to do a demo for you.
@Zane what info would be useful for you to provide and example? I am an intern and just learning SSIS (2 months experience)
Screenshots of your package would probably be helpful. Here's an example of one I have done in the past.
|
0

@[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.

Improve this answer

1 Comment

The FileName has the date in it....I've already wrote a script to trim the FileName for the date.
0

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.

Improve this answer

2 Comments

That only gives Month and Year do you not care what the day is?
the reports are monthly, so I don't care about the day

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.