>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

Theres a good example here

http://stackoverflow.com/questions/7610491/how-to-pass-variable-as-a-parameter-in-execute-sql-task-ssis