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?

score 1 · Answer 1 · edited May 23 '17 at 12:13

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

Pass file name into execute SQL task in SSIS

1 Answers1