SSIS Foreach Loop Container passing variables to SQL Store Procedure

Hello,

I am not sure if this is the right place to ask SSIS and SQL Store Procedure question.  If not, please advise.

I am having a challenge to pass the filename variable from SSIS Foreach Loop Container to SQL store procedure.  I have an "Execute SQL Task" inside "Foreach Loop Container" which will receive the input filename parameter passed by the Foreach Loop Container.  The store procedure command line is defined as "EXEC sp_mySQLStoreProcedure  ?" within the "Execute SQL Task".  The Foreach loop container will gather the filenames from File System then put it in the filename variable one by one.  I would like to pass the filename variable as the input parameter to the sp_mySQLStoreProcedure in the "Execute SQL Task".  How can I connect the variable and the store procedure so that it will process all the input files gathered by the Foreach Loop Container?  Could someone shed some light on this? 

T

September 8th, 2015 12:24pm

Collect the value from foreach loop container in a variable .For which use variable mapping table of container.

Next pass the value pf variable to execute sql task .Depending upon the type of connection used .Parameter passing syntax will change.Please refer below link for more information

https://msdn.microsoft.com/en-us/library/cc280502.aspx

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 12:48pm

Can you be a little more specific?  If I want to pass the variable from Foreach Loop Container as input parameter for the Execute SQL Task, which parameter in the Foreach Loop Container and the Execute SQL Task needs to associate with?  I noticed Execute SQL Task needs to use the Connection to connect to SQL database.  Is there another properties in Execute SQ Task to connect to the filename variable in Foreach Loop Container?

Thanks,

September 8th, 2015 2:06pm

Hi Brian,

Please take a look, this might help

Execute SQL Task (Log File Load Time and Record Count) by using Stored Procedure with Input and Output Parameters in SSIS Package

The demo shows how to create stored procedure, how to use Foreach Loop Container and get the file name, how to use Execute SQL Task and map the variables to Stored Procedure parameters.

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 3:21pm

Thank you. 
September 9th, 2015 1:42am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics