Passing parameters to Batch File and executing batch file loop

PhilWS

Passing parameters to Batch File and executing batch file loop

by PhilWS » Wed, 25 Sep 2013 12:38:07

HELP,

I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.

Please help

Phil



jwelc

Passing parameters to Batch File and executing batch file loop

by jwelc » Fri, 27 Sep 2013 13:39:08

If you want to do this from SSIS, try this:

  1. Use an Execute SQL Task to get a resultset with all the variables
  2. Connect that to a ForEach Loop set to iterate the resultset, and map the value in the recordset to an SSIS variable
  3. Inside the For Each, place an Execute Process task. Set it to call your batch file. You can use an expression to set the Arguments property to the value of your SSIS variable.



jwelc

Passing parameters to Batch File and executing batch file loop

by jwelc » Sat, 28 Sep 2013 15:41:10

Open the property dialog for the Execute Process task. Go the Expressions page (third option in the list on the left). Click the ellipsis (...) button next to the line that says "Expressions". In the resulting dialog, select Arguments from the drop-down list under Property, and the click the ellipsis again under Expression to bring up the expression editor. Drag and drop your variable from the list in the upper left corner to the text box labeled Expression towards the bottom of the dialog. Click Evaluate Expression and you should see the value of your variable. Then click OK until you are back to the control flow. The expression is now setting the Agruments property dynamically with whatever value you put into the variable.




xyke

Passing parameters to Batch File and executing batch file loop

by xyke » Sun, 29 Sep 2013 14:40:09

Question for your step 3, could you tell How to "set the Arguments property to the value of your SSIS variable" Thanks.