Hi.

Hope that someone can point me in the right direction on this.

I need to create a package that does the following:

Retrieve the name of individual csv files stored in a folder (C:\RawInput)
Import the text data into a table of the same name as the raw data text file.

Sounds simple, I know, but I am getting hung up on passing the name of the text file to a stored procedure that is responsible for creating the table (using the name of the raw text file) so that the data can be imported into it.

So far, I've created the Foreach Loop container that is pointing to the files in my RawInput folder. I have also created a variable, varInFile, to house the name of the files as it processes.

I also have the stored procedure that is set up to use a parameter containing the name of the file to create. Executing the sp manually passing it the parameter works. However, when attempting to pass it the @varInFile parameter as part of an EXEC SQL Task, something is going wrong as the package fails when it is time to run the sp.

Here's the content of the EXEC SQL Task: exec spCreateDestination @varInFile

Doesn't like the way the SP is called with the parameter. So, what's the correct way.

Here's the err msg:

Executing the query "exec spCreateDestination @varInFile" failed with the following error: "Must declare the scalar variable "@varInFile".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I've created varInFile in my Foreach Loop container.

Any guidance is greately appreciated.

Vince