pass filename

Using SSIS foreach loop I get the files names inside a folder on the network.
How do I pass this variable i.e. file name to a stored procedure?
Thanks

I've achived something similar to this by doing the following:

(i've assumed you've already got to the stage of populating the variable with the name of the file)

Within your loop:-

1) add an execute sql task and enter the following in your SQLStatement section: - EXEC yourproc ? (the ? is the placeholder for the parameter)

2) set up the parameter to your variable in the Parameter Mapping section remembering to set the correct type. I don't believe the parameter name is important in this scenario.

Hope that makes sense.

|||

This is the error I get:

[Execute SQL Task] Error: Executing the query "exec uspMarketValuesUploadXMLfileReader ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

|||Did you fill out the parameter mapping tab?|||

Yes. Still the same error.

These are my settings

SQLSourceType: DirectInput

ParameterMapping --> User::FileName

datatype: varchar

|||Use a "0" for the parameter name. Also, you won't be able to parse the query.|||

Ah, so it looks like my comment about parameter name not mattering is not quite correct. Apologies. In the parameter mapping section, set the parameter name to be a valid sql parameter eg @.filepath. Hopefully that should do the trick.

Cheers

|||

Solved.

Had to replace 0 with ?

Thanks guys.

|||

Don't forget to mark posts as answered...

Good luck!