Pass file name into execute SQL task in SSIS

Question

I'm busy setting up an SSIS package that takes a flat file and loads its content into a staging table. From there it needs to take the file name of that file and save it into a field on another table, along with one of the lines of data. The loading of the data I do by using an execute SQL task that executes a stored procedure. This procedure takes the file name as input parameter (just so I can write it to that table)... I added a Script task before the execute SQL Task that does the following:

public void Main()
        {
            string inputFolder;
            string fileName;

            inputFolder = Dts.Variables["SourceFolder"].Value.ToString();
           // MessageBox.Show(inputFolder);

            string[] foundFiles = null;
            foundFiles = System.IO.Directory.GetFiles(inputFolder);

            if (foundFiles.Length > 0)
            {
                fileName = foundFiles[0];

                Dts.Variables["ProcessedFileName"].Value = System.IO.Path.GetFileName(fileName);
//                MessageBox.Show(fileName);

               // MessageBox.Show(System.IO.Path.GetFileName(fileName));

            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

The "ProcessedFileName" variable is a global variable in the SSIS package, as well as the "SourceFolder" variable.

The execute SQL task I set up using Direct input executing:

DECLARE @StoredProcedure varchar(100) = ?;
DECLARE @ProcessedFileName varchar(100) = ?;

EXEC ? ?;

The parameter mapping is to variables StoredProcedure and ProcessedFileName.

The problem is when I run this it tells me "No value given for one or more required parameters." Why could this be?

Community · Accepted Answer · 2017-05-23 12:13:18Z

1

You cannot dynamically change a stored procedure name in an Execute SQL Task using parameters. That is what EXEC ? ?; is doing.

If you need to change the procedure name at run-time then you'll need to use string concatenation via expressions to build our your query.

Bigger picture, it seems that you are using a script task where a For Each (file) Enumerator task would suffice. There are reasons to use a script task over foreach (like if you need to process in order, etc) but it doesn't read like you're doing that.

Assorted answers that may be of help on how to accomplish expressions and foreach enumerator

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Jul 26, 2012 at 12:23

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

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

Collectives™ on Stack Overflow

Pass file name into execute SQL task in SSIS

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related